Relative Time periods

[dacke14]

Hi all,

I am trying to create a cube where it is possible to choose a period and get the YTD-value or Running-total 12 for that particular period next to the periods actual values so to say. As far as I know the YTD and other custom calculations in the time period dimension is only relative to the current period. What is needed is a report on this cube that shows a crosstab with for example revenue, budget, ytd, running-total 12 on the columns for any period chosen in a prompt page. I would be glad if this is possible to do in a smart manner in the cube instead of in the database.

How would you sort this out?

thanks in advance

[MFGF]

It depends what you are using to build the report.  If you are using Cognos 8 Report Studio, then this is fairly straightforward in the report.  As your 'current' date is prompted for, there is no way to build this into the cube though.

MF.

[Arvind]

Hello,

Tagging along. I have the same requirement as well. The report studio report uses a powerplay cube. What function can I use to display, let's say, three months before the year-month that was prompted?
Since this uses a powerplay cube, I should be able to use a dimensional function I presume?

Thanks for your help.

[MFGF]

Hi Arvind,

Yes - a dimensional expression will be the easiest and most efficient way to do this.  If you want to see the prompted-for period and the three immediately preceding periods, use an expression such as:

lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?)

Regards,

MF.

[Arvind]

Hi,

this is great! thanks. Also how do i get the measure for that period?

For example if I want to show total_sales_amount for that period what is the syntax should i use?



Thanks again
Arvind.

[Arvind]

this is what I got so far, but does not seem to return the result that I am looking for:

tuple(parallelPeriod ([PCG QOB Homeowners Cube].[Calendar].[Calendar].[Month],-1,[PCG QOB Homeowners Cube].[Calendar].[Calendar].[Month]->?Month?), [Inforce Premium])

[MFGF]

Hi,

this is great! thanks. Also how do i get the measure for that period?

For example if I want to show total_sales_amount for that period what is the syntax should i use?



Thanks again
Arvind.

How about:

aggregate(currentMeasure within set lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?))

or

aggregate([Sales Amount Measure] within set lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?))

Depends if Sales Amount Measure is the only default measure, or whether you want to do this for multiple measures.

Regards,

MF.

[Arvind]

MF:

Worked like a charm. thank you for being such a big help.

Arvind.

[MFGF]

You're welcome! :)