I wish to show a YTD sales on a line chart on a rolling year-to-date x-axis.
For example,
The X axis would be time in month from Nov'14 to Oct'15.
Then the Y axis will show the YTD sales value at each point.
I have used Aggr and Set analysis before to obtain the information in tables but once I use them in charts, the YTD sum does not work properly. Only data points within the time axis of the chart are summed together.
For example, the data point for the Nov'14 YTD in the table will be a sum of sales from Dec'13 to Nov'14.
However, on the chart, it will just show the sum of the sales for Nov'14.
I have seen solutions that involves doing the aggregation in the data loader but I would like the users to be able to see the chart after they have selected filters.
E.g. User can filter by location and department to see how's the YTD sales trend is.
I would greatly appreciates it if anyone can advise me on how to obtain YTD sum for data outside the chart's time axis.
