1 Reply Latest reply: Apr 10, 2018 8:19 PM by Petter Skjolden RSS

    Need help with the below calculation

    Priyaranjan Pattnayak

      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 Skjolden

          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