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 ?
Is this from a dimensional or a relational package?
MF.
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
anyone willing to help? thanks
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.
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