Need help with the below calculation

Anonymous — Tue, 10 Apr 2018 22:42:14 GMT

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.

Re: Need help with the below calculation

petter — Wed, 11 Apr 2018 00:14:17 GMT

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

topic Re: Need help with the below calculation in App Development

Need help with the below calculation

Re: Need help with the below calculation