4 Replies Latest reply: Dec 17, 2012 10:34 AM by Rafael Forchetti RSS

    Question on getting previous year values using date as dimension...

      So, if I have a salesdate date Field and a salesvalue field and need to compare current values against the previous year values, how do I do this ?

       

      I created a straight table chart and used for dimension : MonthName(salesdate).

       

      Then I created the expression Sales Value with :

       

      =Sum(SalesValue)

       

      That correctly gives me the sales value for the correct month-year dimension.

       

      Now I need to create a variation expression that is the sales for the current month / sales of the previous year month. (for example, january 2012 / january 2011)


      I have found dozens of examples but none of them got anywhere near to working, which leads me to believe that those examples don't have date as a dimension of the chart.

       

       

      The only way I could get it to work was by making a if statement outside the sum, considering every single possibility of month-year combination, but I can't believe there isn't a more intelligent way...


      something like :

       

      if(Year(salesdate) = 2012,

      if(Month(salesdate) = 1,

      sum(total if(inmonth(salesdate),makedate(2011,1,1),0) salesvalue)),

      if(month(salesdate) = 2,

      sum(total if(inmonth(salesdate),makedate(2011,2,1),0) salesvalue)),

      ......and so on....

       

      A kinda atrocious solution as you can see.

       

      I can't dynamically set the date into the makedate function because (I believe) of TOTAL, but if I remove the total then it will not have the previous year values to work with because the monthname(salesdate) dimension in the chart will filter them out.

       

      Can anyone offer a better one ?