Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yashpace
New Contributor II

3 Dates, 1 Table, 1 Master Calendar

Hi All,

I am new to Qlik and will really appreciate any help in the following!

I Want to do the following in three different KPI's using three different filters -( 1. DateMasterCalendar 2. WeekMasterCalendar, 3. MonthMasterCalendar)  :  -

1st KPI: - 

IF (  DateMasterCalender = [Add Date], COUNT(Distinct ORDNUM)) ,

IF ( WeekMasterCalender = Week([Add Date]), COUNT(Distinct ORDNUM),

IF ( MonthMasterCalender = Month([Add Date]), COUNT(Distinct ORDNUM)))

2nd KPI:-

IF (  DateMasterCalender = [Processed Date], COUNT(Distinct ORDNUM)) ,

IF ( WeekMasterCalender = Week([Processed Date]), COUNT(Distinct ORDNUM),

IF ( MonthMasterCalender = Month([Processed Date]), COUNT(Distinct ORDNUM)))

3rd KPI:-

IF (  DateMasterCalender = [Dispatch Date], COUNT(Distinct ORDNUM)) ,

IF ( WeekMasterCalender = Week([Dispatch Date]), COUNT(Distinct ORDNUM),

IF ( MonthMasterCalender = Month([Dispatch Date]), COUNT(Distinct ORDNUM)))

 

Sample data:-  

If User Choose from DateMasterCalender Filter - 8/30/2019

This should result in KPI 1 - 12  (Add Date), KPI 2 -  4 (Processed Date),    KPI 3 - 2 (Dispatch Date)

SampleScreen.JPG

 

TIA!

 

1 Solution

Accepted Solutions
asinha1991
Contributor III

Re: 3 Dates, 1 Table, 1 Master Calendar

can do something like this.

Temp:

Load ORDNUM, AddDate as DateKey,

'Add Date' as DateType

Resident table

Concatenate 

 

Load ORDNUM, ProcessedDate as DateKey,

'ProcessedDate' as DateType

Resident table

Concatenate 

 

Load ORDNUM, DispatchDate as DateKey,

'DispatchDate ' as DateType

Resident table

 

Now create a master calendar with DateKey as key

example

MasterCalendar:

Load distinct DateKey 

week(DateKey)

month(DateKey) ...Resident Temp

 

in your KPI just mention date type and then your calendar will work

example

first KPI: COUNT({<DateType={'Add Date'} >}Distinct ORDNUM)

3 Replies
michael_graham
New Contributor

Re: 3 Dates, 1 Table, 1 Master Calendar

What data would be populating the week and month filters?  Would those be dates as well?

yashpace
New Contributor II

Re: 3 Dates, 1 Table, 1 Master Calendar

Hey Michael,

Yes, filter 1 - DateMasterCalednar,  filter 2 will be WeekMasterCalendar, filter 3 - MonthMasterCalednar.

 

Basically I want to COUNT how many Orders were Added, Processed, Dispatched on any given Day, Week, Month. 

 

Thanks a ton!!

 

Capture.JPG

asinha1991
Contributor III

Re: 3 Dates, 1 Table, 1 Master Calendar

can do something like this.

Temp:

Load ORDNUM, AddDate as DateKey,

'Add Date' as DateType

Resident table

Concatenate 

 

Load ORDNUM, ProcessedDate as DateKey,

'ProcessedDate' as DateType

Resident table

Concatenate 

 

Load ORDNUM, DispatchDate as DateKey,

'DispatchDate ' as DateType

Resident table

 

Now create a master calendar with DateKey as key

example

MasterCalendar:

Load distinct DateKey 

week(DateKey)

month(DateKey) ...Resident Temp

 

in your KPI just mention date type and then your calendar will work

example

first KPI: COUNT({<DateType={'Add Date'} >}Distinct ORDNUM)