Rolling 12 Month Sales using Set Analysis Chart Sort?

[Anonymous]

Hello

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)

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

[Anonymous]

Hi Alex I am currently downloading the app you sent (Slow connection lol) I will take a look, for now you should be able to put your expression for calculating the first date into the sort by expression tab in the properties menu, in my case I changed the sum function for max and it worked like a charm. If that doesn't work you can do a cheeky cheat and load in your created dates with rowno() you can then sort by rowno(),this must be done in the script.

[#3]

Man, I thought i had tried the max(As of Date) before...

Anyways, just tried that and it worked perfectly!