Building calculated measure like Year-To-Date

[Joern]

Hello,

I'm just starting to test Cognos Dynamic Cubes. I have successfully design my first cube with 4 dimension and a time dimesion with current periodes declared.
Everthing looks fine with default mesures coming from the basic fact table.

But now I want to build a calculated measure like a total YEAR-To-Date value. How must I do this? I have searched now for two days to find a solution, but I was not successful.

I'm coming from Oracle-OLAP, so maybe I'm thinking in the wrong direction, but I guess this must be very easy or not?

Thanks for any help.

[MFGF]

Hello,

I'm just starting to test Cognos Dynamic Cubes. I have successfully design my first cube with 4 dimension and a time dimesion with current periodes declared.
Everthing looks fine with default mesures coming from the basic fact table.

But now I want to build a calculated measure like a total YEAR-To-Date value. How must I do this? I have searched now for two days to find a solution, but I was not successful.

I'm coming from Oracle-OLAP, so maybe I'm thinking in the wrong direction, but I guess this must be very easy or not?

Thanks for any help.

Assuming you're on a recent build of Cube Designer, you can add Relative Time members like this to your time dimension.

First, make sure it's defined appropriately as a time dimension:
- Select the time dimension and in the Properties pane set Dimension Type to 'Time'
- Select each level and set its Level Type property appropriately (eg the Month level should be set to Months)

Next, add default Relative Time members:
- Select the time hierarchy and set its Add Relative Time Members property to True

Finally, if desired, you can add your own custom relative time members:
- Select the time hierarchy and choose the Relative Time tab
- There are buttons on the right to allow you to create Custom Single Period, Custom Period To Date and Custom N-Period Running Total relative members.

Cheers!

MF.

[Joern]

Thanks for your answer.
I have already done this and it is working fine. In my time dimension I have the default members coming form the definition of "running period"

But I don't know how I get my Year-To-Date mesure now?

[Joern]

May be I should explain a little more in detail what I want.

Example:
I have the following crosstab:

                January   February   March
Customer 1   1000              700   300
Customer 2      500      800   300
Customer 3      600      200   600

The values here are the monthly total revenue for each customer. No problem here, this data is coming directly from the basic fact table.

And what I want now is this:

                January   February   March
Customer 1   1000              1700   2000
Customer 2      500      1300   1600
Customer 3      600      800   1400

Now the values are what I called the YEAR-TO-DATE revenue for each customer.

How do I create this measure?

[schrotty]

Hello Joern,

You can create a calculated Measure (not a calculated member!) in your cube by using following syntax:

aggregate([YourMeasure] within set( PeriodsToDate ( [Year Level], currentMember([Your Time Hierarchy]) ) ) )

Replace the Bold Text Items with your Measuer/Level/Hierarchy!

In the studios your new measure will calculate the YTD (or YTM, ....)

If you have more than one Time-Hierarchy its a little bit more complex, because you have to find out in which Hierarchy you are...


Use the Functons tab in cube designer to read more about the used functios.
Nice link :
http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page563.html

Schrotty :)

[Joern]

Hello schrotty,

that seems to be the answer. Thank you.
It is working fine in report studio.
I can build this measure in the Cube Desginer with no errors, but after publishing the cube and open it, I don't see this measure.

Any idears?

[schrotty]

Hi Joern,

In my expression was a syntax error at the end (missing ")" ). please check yours

If that doesn't fix it. Do you can post your expression and the structure of your Time-Dimension/-Hierarchy ?

Mfg schrotty

[Joern]

Hello schrotty,

this is my measure:

aggregate(Umsatz Erloese within set (PeriodsToDate(JAHR,currentMember(TAG))))

It seems ok for me, but it is not working.


I don't know how I should give you the structure of my time dimension.

[Joern]

Hello,

I just want to let you know how I solve this problem.

Correct is the follwoing syntax;
aggregate(<BASIC measure> within set (periodsToDate(<LEVEL>;currentMember(<HIERARCHIE>))))


My Many thanks to schrotty who helped me here to solve the problem in a great way.