Running Sum

[Bogdan]

Hello,

I want to construct a running total. The data goes down to month level. I want to click on 2007 and then see the month figures cummulative I.e.

Jan = Jan
Feb = Jan + Feb
March = Jan + Feb + March
etc...

In principle it's a YTD but then for each Month.

The relative time option (running sum for N Month) is fixed number of month. I want to re-start always the 1st January.

How do I create this?

Thanks for your support,
Bogdan

p.s. I'm using Transformer 7.0.

[nonooo]

And what should you see on top ?
I mean:

jan = 5                =5
feb = 5 + 3          =8
mar = 5 + 3 + 9    =17

=> Q1 = 17 
=> Q1 = 30

I am not sure but I think in case of 17 you have to play with a special category (because top is not a rollup of down) BUT in case of 30 you can play in SQL.

hope this help

[Bogdan]

There is no requirement to see Quarterly figures. Just the separate months as per the example

jan = 5                =5
feb = 5 + 3          =8
mar = 5 + 3 + 9    =17

Unfortuantely not the answer yet, any help is appreciated

Bogdan

[almeids]

As Nonooo suggests you can do this in the data/SQL if you're not concerned about the rollup; you could also use a last-period time state rollup to preserve the YTD values at higher levels (Q1=17 in the example).  Depending on how/where you need this you may also be able to simply use a calculation (operation type Financial, operation Accumulate) in a saved view of your cube.

[Bogdan]

Hi
I have used the built-in Reporter funtion from Powerplay and indeed that allows you to calculate Accumulation on the fly.

However we are using Cognos 8 with the "Analys Studio" to share the CUBE's instead of Powerplay reports. Do you know how to do this trick in Analysis Studio?

In addition, can you show some detail more about the "last-period time state rollup". Where should is build this in SQL DB, Framework, Transformer, Analysis Studio?

Thanks,
Bogdan

[nonooo]

you will be pleased to know that in reportnet the function is called "running-total"
and you add a clause  "within set" so it restart each year...
==> running-total (<measure_per_month> ) within set <year>
I have not checked in report studio but the syntax must be close to this..

Anyway this is not the "good" way to do because each time a user will connect he will force the server to calculate (that is a bit lame if you built a cube prior) so you should definitively try to do a SQL query and change your datasource in transformer.

this is only what I think, I may be wrong (I am often wrong :))

[almeids]

Sorry, can't help with C8/Analysis studio, not there yet.
Time state rollup is a measure property, on the rollup tab, in Transformer.  It controls rollup behavior specifically as it pertains to your time dimension and would classically be used for point-in-time snapshot values such as inventory balances which you would not want to add up across time periods.
You would only need to change to an end-of-period time state rollup if you had already embedded the YTD values in your data AND you wanted to let Cognos handle time dimension rollup without multiple-counting prior period values.

[Cannondale23]

here is a write up of how I would approach this request. I've always wanted to apply a forward projected special time technique in the mdl but no one ever asked for it.

glad you posted...enjoy