11 Replies Latest reply: Apr 25, 2012 12:46 PM by Alex Peasley RSS

    Rolling 12 Month Sales using Set Analysis Chart Sort?

    Shay Cooper



      I am struggling with the following problem, i have a chart that need to compare over months this years sales and the previous years sales however the graph needs to be dynamic in that it changes in accordance with the users selections, the following is my current expression;


      Current Sales: sum({1<OrderDate={">=$(=addmonths(monthend(max(OrderDate))+1,-12)) <=$(=max(OrderDate))"}>}Sales)


      Previous Sales: sum({1<OrderDate={">=$(=addmonths(monthend(max(OrderDate),)+1,-24)) <=$(=addmonths(max(OrderDate),-12))"}>}Sales)



      % difference: (Column(1)-Column(2))/Column(1)


      12 months rolling.jpg

      This works fine when a year is selected however when i select a month for example if the Fiscal Year 08/09 is selected as above and i click the month of May what i would like to see is Jun 08 to May 09 (Current Sales) and Jun 07 to May 08 (Previous Sales), this works with regards the dates but the order of the Month in the chart does not relate properly.


      Any help would be greatly appreciated.


      Message was edited by: shay.cooper I have managed to sort this with the help from colleagues at work, basically it involves using the same expression used for the current sales and replacing sales with MonthYear and adding max to the start rather than sum and works like a charm.


      Message was edited by: shay.cooper Added an example of the graph with sample data please excuse percentage change as the data was created using rand function so make no sense really