help needed: 3 month rolling computation

[akosijeremy]

I need to have a 3 month rolling computation for the sample report attached. Item A and  Item B would have 3 month rolling total and the third row would be Item A/ Item B also with a 3 month rolling computation. Appreciate your inputs. Thanks

Year                       2009            
Month  1               2        3         4      Three Month Rolling
Item A   521,563  569,992  549,548  540,548                ?
Item B   1,620,060  1,515,583  1,436,797  1,400,797   ?
Item A/ Item B 0.32  0.38  0.38  0.39                            ?

[MFGF]

Is this from a dimensional or a relational package?

MF.

[akosijeremy]

it's from a relational package sir. i tried adding a data item to the query with moving average and moving total function but i can't get it to work. i'm not sure if i need to change aggregate or rollup aggregate or maybe the entire approach is wrong. i tried it on a crosstab. i'm new at using report studio so appreciate any help

[akosijeremy]

anyone willing to help? thanks

[MFGF]

Hi,

If your are using a relational package, this can be quite tricky.

You will probably need to define a second query to calculate your threee-month running total, then join this back to the original query and use the results of this in the query which drives your crosstab.

MF.

[akosijeremy]

Thanks MF. I'll look into that too.

I tried something here but somehow I'm getting a spillover calculation on April and May where there's no Headcount data. It's weird since after Mar it begins to do like a rolling subtraction. If you notice Mar Headcount and May Moving Headcount is the same. Looks something like this:


                                       2009
                                       Jan  Feb    Mar    Apr      May
Group 1 Headcount            198   177   158.5
Group 1 Moving Headcount  198   375   533.5 335.5  158.5


Moving Headcount is:

moving-total([Headcount],3 for [Year],[Group])

Appreciate your inputs on how to get this to work right. Thanks