25 Replies Latest reply: Jan 10, 2012 1:55 PM by Debbie Pyykkonen RSS

    Showing values for previous 12 months/periods using set analysis?

    warrwarrwarr

      Hi,

      I want to make a chart with the sum of "quantity" for the current period and for 11 previous periods. I.e. 12 months/periods.

      I'm using the following expression in a chart:

       

       

      =



      sum({$<created_per={$(=Max(created_per))}>}quantity)

      created_per is the period in format YYYYMM. This expression works fine, and shows the latest period in my chart.

       

       

      However, if i write expressions like this for each record:



       

       





       

      = sum({$<created_per={$(=Max(created_per)-1)}>}quantity)

      = sum({$<created_per={$(=Max(created_per)-2)}>}quantity)

      = sum({$<created_per={$(=Max(created_per)-3)}>}quantity)





      It does not work, because when subtracting 1 from the period 201101, it becomes 201100 - which obviously is not a valid period. Instead of 201100 I would of course need to get 201012.

       

      Any suggestions for how to solve this? How do I refer to the previous record in the created_per "column".