Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a PeriodStart and Sales columns in my data which look like this.
PeriodStart | Sales
03/30/2018 03:00:00 AM 120
03/30/2018 02:00:00 AM 115
.
.
.
03/23/2018 03:00:00 AM 110
.
.
03/16/2018 03:00:00 AM 100
.
.
03/09/2018 03:00:00 AM 90
I want a calculation(measure) that gives something like (120 - (110+100+90)/3) i.e Sales on current datetime (03/30/2018 03:00:00 AM) - avg(Sales on same weekday/same hour a week ago + two weeks ago + 3 weeks ago)).
I am new to Qlik. Any help is appreciated.
The expression you can put in your KPI is:
Sum({<PeriodStart={
'$(=Date(Max(PeriodStart) ,'M/D/YYYY hh:mm:ss tt'))'
}>} Sales)
-
Sum({<PeriodStart={
'$(=Date(Max(PeriodStart)- 7,'M/D/YYYY hh:mm:ss tt'))' ,
'$(=Date(Max(PeriodStart)-14,'M/D/YYYY hh:mm:ss tt'))' ,
'$(=Date(Max(PeriodStart)-21,'M/D/YYYY hh:mm:ss tt'))'
}>} Sales)/3
Dates is a pain in set expressions unless you work with pure numeric dates without formatting....
If you create a variable:
vMax0 =Date(Max(PeriodStart),'M/D/YYYY hh:mm:ss tt')
vMax7 =Date(Max(PeriodStart)-7,'M/D/YYYY hh:mm:ss tt')
vMax14 =Date(Max(PeriodStart)-14,'M/D/YYYY hh:mm:ss tt')
vMax21 =Date(Max(PeriodStart)-21,'M/D/YYYY hh:mm:ss tt')
Then the expression can be:
Sum( {<PeriodStart={'$(vMax0)'}>} Sales)-Sum( {<PeriodStart={'$(vMax7)','$(vMax14)','$(vMax21)'}>} Sales)/3