Northwind Sales Dashboard in Qliksense| Canonical Dates| Customer Retention and Churned rate| Dynamic Set Expression

Hello! I hope you all are doing great!

I am back with another project and like the previous dashboards I created, I learned a lot developing this one too!

Let's start with the dataset. Looking at the tables, and date fields, I knew what I had to achieve the desired data model.

Canonical Date:

There are 3 dates - Order, Shipping, and Expected Delivery Date. The obvious way was to create a canonical Calendar with a bridge table linking the Master calendar with the rest of the data model.

I created a subroutine for the Master Calendar table creation and the bridge table to avoid writing again the almost same piece of code.

Master Calendar

sub Master_calendar(vDate, vTable,vCan)

MinMaxDate:

Load

[$(vDate)] as [Date]

Resident '$(vTable)'

where len(trim([$(vDate)]))>0

order by [$(vDate)] asc

;

Let varMinDate = peek('Date', 0, 'MinMaxDate');

Let varMaxDate = peek('Date', -1, 'MinMaxDate');

trace '$(varMinDate)', '$(varMaxDate)';

drop Table MinMaxDate;

CanonicalDate:

Load

YearStart(date#('$(varMinDate)','DD/MM/YYYY')) as CanonicalDate

AutoGenerate 1;

Load

YearEnd(date#('$(varMaxDate)','DD/MM/YYYY')) as CanonicalDate

AutoGenerate 1;

if '$(vCan)'='C' then

[Master $(vDate)]:

*************Date Fields****************

Load

Date(num(date#('$(varMinDate)', 'DD/MM/YYYY'))+ IterNo() - 1,'DD/MM/YYYY') as Date

AutoGenerate 1

While num(date#('$(varMinDate)', 'DD/MM/YYYY'))+ IterNo() - 1 <= num(date#('$(varMaxDate)', 'DD/MM/YYYY'));

Endif

End Sub;

Call Master_calendar('Order Date','Orders_Final','M');

Call Master_calendar('Expected Delivery Date','Orders_Final','M');

Call Master_calendar('Shipped Date','Orders_Final','M');

Call Master_calendar('CanonicalDate','CanonicalDate','C');

The above code will calculate the minimum and maximum dates for each field and create a Master Calendar between that date range.

Date Bridge

Sub Mapping(vDate,vTable,vMapID, vBridgeID, vBridgeTable,vCan)

[Map_$(vDate)]:

Mapping

Load

[$(vMapID)],

date([$(vDate)],'DD/MM/YYYY') as [($(vDate)]

Resident $(vTable);

DateBridge:

Load

[$(vBridgeID)],

date(Applymap('Map_$(vDate)',[$(vMapID)],Null()),'DD/MM/YYYY') as CanonicalDate,

'$(vDate)' as DateType

Resident [$(vBridgeTable)];

End Sub;

Call Mapping('Order Date','Orders_Final','orderID','OrderDetailsID','Order_Details','C');

Call Mapping('Expected Delivery Date','Orders_Final','orderID','OrderDetailsID','Order_Details','C');

Call Mapping('Shipped Date','Orders_Final','orderID','OrderDetailsID','Order_Details','C');

I always try to write code that would allow me to reuse it in my future work. Similarly, the above sub will do a mapping load for the tables and fields passed and create a bridge table.

KPI Set Expression

The KPIs in the Dashboard are calculated with a Set expression using variables as set modifiers, one expression will give the results for different selections:

{<$(vDateField)={'$(vPrevDateValue)'}$(vExclude)>}[Net Revenue ($)]

Container chart to switch charts

We all miss Qlikview's ability to hide charts based on conditions and replace that area with a different object. It's achievable in Qliksense as well using Containers! We can place our graphs, and tables inside a container and hide the tabs. and based on show conditions, the container tabs will be activated and hidden. In the additional images, you can see I have done the above using button triggers to switch between table and combo chart for top performers, and trend over time combo and line chart.

Customer Retention and Churn Rate using P and E

In any analytics involving Sales, the stakeholders would like to have a view of the customer loyalty to their brand.

Set expressions involving P and E functions can help us achieve the correct numbers.

Lost:

Count({<$(vDateField)= {'$(vPrevDateValue)'} $(vExclude),customerID = e({<$(vDateField) = {'$(vMaxDateValue)'}>} customerID)>} DISTINCT customerID)

Retained:

Count({<$(vDateField)={'$(vMaxDateValue)'}, customerID = (p({1<$(vDateField) = {'$(vMaxDateValue)'}>} customerID)* p({1<$(vDateField) = {'$(vPrevDateValue)'}>} customerID)) $(vExclude)>} DISTINCT customerID)

New:

Count({<customerID=E ({1<[Order Date] = {"<=$(vMinDate)"}>})>} Distinct customerID)

There are some variables I have used in the expression to limit the expression size - like $(vExclude) the field selections to ignore in the set dynamically based on selection.