Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
bugsysiegals
Contributor II
Contributor II

1 Fact Table - Different Date Fields per KPI - 1 Master Calendar

I've created a summary page which presents KPI's and deltas related to their respective goals.  I've two KPI's, calculated from the same fact table, which are reported by the YearMonth of two different date fields within the fact table.  Is it possible to link a fact table to a single master calendar table, by both date fields, causing a circular loop, and still calculate the KPI's correctly by adjusting the set analysis?

The reason I'm trying to use a single master calendar table is because I've detailed versions of the application which are regional and allow drill down and a single global summary version.  Because of the size of details, I cannot have all the global details in a single application.  In order to keep everything consistent as the application continues to grow with new KPI's and all around easier to maintain, I'm trying to have a single master application which uses variables to load regional details or global summary which I can set variables, load, and save with different application name.  The global summary ends up with a single table linked to the master calendar and to keep field names the same, I'd like to try and do this with a single master calendar for the detailed version of the application.  Is this possible or should I just create multiple master calendars and adjust the set analysis accordingly?

Below is a high level image of my detail data model ... FWIW - the Analysis table has a field called Analysis which has values of MTD, PMTD, CY, PYTD, etc.  This is displayed on the front end in drop down and change the KPI through set analysis.

Qlik Sense Data Model.JPG

Here's my expression for On Time Shipment KPI which is reported in the YearMonth a Sales Order Line "should" have shipped (DT_PREVIEWED_SHIP) ...

if(Analysis='MTD', Sum({<YearMonth={"$(=vMaxYearMonth)"}>}[NR_LINES_SHIPPED_ONTIME]) / Count({<YearMonth={"$(=vMaxYearMonth)"}NR_LINES_DUE]), 
if(Analysis='PMTD', Sum({<YearMonth={"$(=vPreviousYearMonth)"}>}[NR_LINES_SHIPPED_ONTIME]) / Count({<YearMonth={"$(=vPreviousYearMonth)"}>}[NR_LINES_DUE]),
if(Analysis='YTD', Sum({<Calendar.FL_YTD={1},YearMonth=>}[NR_LINES_SHIPPED_ONTIME]) / Count({<Calendar.FL_YTD={1},YearMonth=>}[NR_LINES_DUE]), 
if(Analysis='PYTD', Sum({<Calendar.FL_PYTD={1},YearMonth=>}[NR_LINES_SHIPPED_ONTIME]) / Count({<Calendar.FL_PYTD={1},YearMonth=>}[NR_LINES_DUE]),
            if(Analysis='PY', Sum({<[Calendar.FL_PY]={1},YearMonth=>}[NR_LINES_SHIPPED_ONTIME]) / Count({<[Calendar.FL_PY]={1},YearMonth=>}[NR_LINES_DUE]),'')))))

 

Here's my expression for #Lines Shipped KPI which is reported in the YearMonth the Sales Order Line really shipped (DT_REAL_SHIP) ...

if(Analysis='MTD', Sum({<YearMonth={"$(=vMaxYearMonth)"}>}[NR_LINES_SHIPPED]), 
	if(Analysis='PMTD', Sum({<YearMonth={"$(=vPreviousYearMonth)"}>}[NR_LINES_SHIPPED]),
		if(Analysis='YTD', Sum({<Calendar.FL_YTD={1},YearMonth=>}[NR_LINES_SHIPPED]), 
			if(Analysis='PYTD', Sum({<Calendar.FL_PYTD={1},YearMonth=>}[NR_LINES_SHIPPED]),
            	if(Analysis='PY', Sum({<[Calendar.FL_PY]={1},YearMonth=>}[NR_LINES_SHIPPED]),'')))))

 

0 Replies