Need help with the below calculation

[Anonymous]

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.

[petter]

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