3 Dates, 1 Table, 1 Master Calendar

[yashpace]

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)

 

TIA!

[michael_graham]

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

[yashpace]

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!!

 

[asinha1991 · Accepted solution]

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)