Comparing Relative Time Periods

[Rolldice]

Hello Cognos Colleagues,

I have a time dimension with a Fiscal Year dimension of Yr, Quarter, Month I also have another drill down of 6 month blocks called ‘Period Of Activity’ POA. During a given year POA1 and POA2.

What I would like to do is to compare POA2 with POA1 within the same year – as a relative grouping, such that if you are in month 3 of POA2 my measure will compare that POA’s 3 months total against the POA to date in POA1 (3 months) Total.

I would like to achieve two measures:

1 – Growth Previous Period POA (POA2 – POA1)
2 – Growth Previous Period POA (POA1_Yr2 – POA_Yr1)

I have been able to achieve these using Special categories. However I need to do this as a measure, in order to have consistence between my cubes.

Has anybody done this before?

Will I have to work this out at a database level and have the measure as external rollups?

Please help..


----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[yksvaan]

Yes, you have to work this out at a database level. But have the measures time state rollup property to be Last period, and make db queries that return the cumulative amount for each regular month. So for each month, you have the cumulative value for that month, but the months won't add up. Done it and works.

This was short version, but hope you got the idea.

The win is that, you don't need to get lost in the world of external rollups.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[Rolldice]

yksvann,

Thanks for your reply. Apologises, I'll been out of acton with a while.

Are you saying that with the POA (6 month period) to add up the cumulative amounts for where you are in the POA. I think I'm with you on that.. And then set the measures time property to last. example:

POAm1
POAm2 (m1+m2)
POAm3 (m1+m2+m3)
POAm4 (m1+m2+m3+m4)
POAm5 (m1+m2+m3+m4+m5)
POAm6 (m1+m2+m3+m4+m5+m6)

Cheers again

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[yksvaan]

There are several ways of implementing depending on how you exactly want the cube to work with user. What should happen when the user filters to a certain month 1) in the 1st POA 2) in the 2nd POA? How about a separate dimension Periods of POA, one categories being 1-x.

Do you really want the following as a measure
2 – Growth Previous Period POA (POA1_Yr2 – POA_Yr1)

You can never achieve this without bringing the numbers (with sql) into a separate measure for previous year, in PowerPlay you can never "convert" relative time categories to measures.

The sql might be a bit challenge, but a sql programmer is able to present it when the requirements are clear. Don't think it could be done with Impromptu ui, needs hand-written sql.

You don't have to have all this as measures
POAm1
POAm2
POAm3
POAm4
POAm5
POAm6
Unless you want so.

I am able to help you with the sql, if you clarify the requirements. BTW, good approach is to make a "test" cube with just some generated (excel) numbers, that look real, and "tour" the cube structure with users. Then they normally are able to tell what they exactly want.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[Rolldice]

Cheers for the reply,

I thought that it would come down to some heavy SQL.

I'm going to put it to one side for now -

Thanks for your input.


Rdgs



----------------------------------------
Another throw closer to a win ... ???
----------------------------------------