year to date in set analysis for KPI's

[beat_roos]

Hi there 

I am using a set analysis to get the revenue of the actual year. What works really good. 
like this:    sum({<Jahr = {$(=Max(Jahr))}>}Umsatz)   (Jahr means Year, Umsatz means Sales) 
Now I do the same to get the revenue of the previous year sum({<Jahr = {$(=Max(Jahr -1))}>}Umsatz)

So really easy to get my revenue for this year and prev. year. But now i want to compare the same time frame in both years.
So as an example If the actual date is April 26 2021 the prev. year to date should give me the revenue for the same time frame what means 1.1.2020 to 4.26.2020. 

How can I solve that within a set analysis? My target ist to get 

Revenue of max year (done) 
Revenue of max year to date - 1 year (?) 
Revenue of prev year (done) 

in one table. Thanks a lot for your help! 
Cheers, Beat

[Dalton_Ruer · Accepted solution]

Best way to handle this is by creating  a Master Calendar for the Date values in the Load Script. So that each date has a set of flags like Current Year to Date, Previous Year to Date, Current Quarter, Fiscal Year etc. 

Then in your KPI's you do simple math:

Sum(PreviousYearToDateFlag * Value)

Just search for Qlik Master Calendar and you will find a ton of examples. 

[beat_roos]

do you have a good script example to share for such a master calendar? would be very helpfull 🙂 THX!

[CHansford]

Beat,

This autoCalendar, which is auto-generated from the Qlik Sense Data Manager, when you have a date, date/time stamp field in your loaded data, is generally sufficient for most needs, although I am sure @Dalton_Ruer  can come up with any number of superior ones.

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [dateTime], [Date], [Reading Time] USING [autoCalendar] ;

Stay Safe

CHris

[Dalton_Ruer]

Here is a handout from a webinar I did years ago. Contains many different examples and code.