Quarterly trend chart to show trailing 4 quarter numbers

[#1]

Hi,

I have been asked to develop a bar chart (with 5 bars, dimension-X is the fiscal quarters sorted in descending order, dimension-Y is the accumulated sales) which shows :-

BAR 1: Q2 FY12 (current quarter) - total for Q2 FY12 until Q3 FY11 (meaning totals for current quarter plus previous 3 quarters)
BAR 2: Q1 FY12 - total for Q1 FY12 until Q2 FY11 (meaning totals for Q1FY12, Q4FY11, Q3FY11 and Q2FY11)
BAR 3: Q4 FY11 - total for Q4 FY11 until Q1 FY11 (meaning totals for Q4FY11, Q3FY11, Q2FY11 and Q1FY11) 
BAR 4: Q3 FY11 - total for Q3 FY11 until Q4 FY10 (meaning totals for Q3FY11, Q2FY11, Q1FY11 and Q4FY10) 
BAR 5: Q2 FY11 - total for Q2 FY11 until Q3 FY10 (meaning totals for Q2FY11, Q1FY11, Q4FY10 and Q3FY10)

May i know what would be the simplest way to achieve this? I have been trying to use the Expression Accumulation (with 4 steps back - pls see attched file). Unfortunately, there are two problems with this approach: 1) the bars will have to be sorted in accending order instead of descending order. 2) I will have to show eight bars (ie from Q2FY12 to Q3FY10) instead of the requested 5 bars (ie from Q2FY12 to Q2FY11) in order to get the accumulation works. Kindly please help me as this is an urgent request from the user. Thanks so much.

[Oleg_Troyansky]

Hi,

I usually solve problems like this in the data model. You need to build an alternative association for this particular "Quarter" field and the regular Quarter (or Month, or Date...).

Create a new table - call it Trailing4Quarters, for example. In  this new table, you'll need 2 fields:

1. Your regular Quarter field from the Calendar

2. The new quarter field, you can call it "Trailing4Quarter".

Load pairs of Quarter values based on the desired association, just the way you described them in your question. Each "Training4Quarter" will appear 4 times, withe the 4 Quarters that you need to aggregate for this chart.

Once you have the table, use the new field as your Dimension, and the rest will happen by itself...

cheers,

Oleg