2 Replies Latest reply: Nov 23, 2015 4:08 AM by Luis Ibáñez RSS

    Measure as PreviousPeriod's value

      Hi everyone,

       

      If I have an expression like Sum(Amount) that aggregates across all dimensions (dates, months, years, products, etc), can I define another expression that aggregates the same measure across all dimensions but for the previous period?

       

      In my SSAS mind, this is as simple as:

      ([Date].currentmember.PREVMEMBER,[Measures].[Amount])

       

      In SSAS I dont even need to specify whether the previous period is a day, month or year because SSAS understands that it needs to aggregate "Amounts" at the same Date Level that is being queried. But it wouldnt be a problem to create a different measure for each level.

      This way, if a date does not contain data, it can still contain previous period's data. For example, if there were no sales for "Bike" products in March, this month should still be shown if February (previous month) had sales.

       

      does it make sense? There has to be a simple way to show a "previous period" measure to be able to, for example, calculate increases in sales, etc.

       

      Example:

       

      Data

      DateProductAmount
      01/01/2015Bike3
      01/01/2015Accesories4
      01/02/2015Bike5
      01/02/2015Accesories6
      01/03/2015Accesories7
      01/04/2015Accesories8
      01/04/2015Bike9

       

      Aggregated across months:

         

      MonthSum(Amount)Sum(Amount) PREV_MONTH
      17
      2117
      3711
      4177
      5 17

       

      Aggregated across months and product:


           

      MonthBikesAccesories
      MonthSum(Amount)Sum(Amount) PREV_MONTHSum(Amount)Sum(Amount) PREV_MONTH
      13 4
      25364
      3 576
      49 87
      5 9 8

       

      Thanks a lot

        • Re: Measure as PreviousPeriod's value
          Anand Chouhan

          Hi,

           

          Try this ways

           

          LOAD *,Month(Date) as DMonth,NUM(Month(Date)) as NMonth;
          LOAD Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,Product, Amount;
          LOAD * INLINE [
              Date, Product, Amount
              01/01/2015, Bike, 3
              01/01/2015, Accesories, 4
              01/02/2015, Bike, 5
              01/02/2015, Accesories, 6
              01/03/2015, Accesories, 7
              01/04/2015, Accesories, 8
              01/04/2015, Bike, 9
          ];
          

           

          Prev.PNG

           

          Check the attached qlik file for solution.

           

          Regards,

          Anand

            • Re: Measure as PreviousPeriod's value

              Thank you very much Anand,

               

              Interestingly, the example seems to work fine for this pivoting table, but not for graphs (?). I added 2 charts with both measures, Sum(amount) and Above(Sum(amount)). The first chart is grouping by Product and NMonth; The second chart is grouping by NMonth and Product (the other way around). These are the results:

               

              Sin título.jpg

              As you can see, the first chart for Month 3 and Bike Product is not showing "previous month sales". Instead Month 4 is showing 5 as "previous month sales".

              The second chart does not show any previous month sales at all for Accesories.


              Kind regards