4 Replies Latest reply: Jan 24, 2013 5:15 AM by raksh208 RSS

    Set analysis expression for 13 month trend.



      I need to write a set analysis expression for the below condition.


      Difference of Amount between current month and previous month for their respective bands. And this difference has to be calculated across 13 months.


      Lets say, I have data from Nov 2011 to Nov 2012. When I select a particular month(say Aug 2012), the expression should return difference in Amount for Aug 2012(current month) and july 2012(previous month). And when no specific selections are made, I should get a graph for all 13 months showing differences of amount respective to their previous month.



      I started out writing this expression, but it would give result only for Nov 2012 and Oct 2012(since that is the current and previous month in my data).

      How do i get it for the remaining months as well?


      sum({<[Month Year] = {$(=Addmonths(max(MonthYear),0))}, Band ={'A'}>}Amount) - sum(({<[Month Year] = {$(=Addmonths(max(MonthYear),-1))}, Band ={'B'}>}Amount).


      Note: Month Year is in MMM-YYYY format.


      Any help would be appreciated.