YTD measures

[Flybridge]

This is similar to a question posted a few lines below, but I can't figure it out.

I have a time dimension wich is just Yearerod.
The financial data is stored as a period figure.

I need to create YTD figures up to the current period being looked at. I have used the sum function and arrived at this.

Sum([Periods].[Year].Members)

However, this returns a year to date for all periods processed and populated in the cube at this point in time, so for a past year I get the annual figure.

I tried to amend it to calculate a YTD figure up to and including the period under review and ended up with

Sum([Periods].[Year].[Periods].[Month].Dimension.CurrentMember)

But it doesn't like this.

My only exposure to MDX education was 1 hour in a 1 week OLAP course, so I expect I have made a silly and obvious error (to someone who knows what to do).

Any help would be greatly appreciated, as the only other option seems to be to create a number of specific calculated members, one for each period in the cube. I'm sure there must be a better way than that!

 

[BKhanna]

I was able to get this to work. I created a new dimension 'Periodicity' with one member called 'Normal View'. I had to add a key to my fact table for this dimension with 'Normal View' on every row. Then I created a calculated member in the periodicity dimension, called 'Period To Date View' and set its value as below:

Sum( PeriodsToDate(), [Periodictiy].&[Normal View])

When the user selects the period to date view, he will see the to-date value for every measure upto the period selected in the time dimension (except for 'All' level of the time dimension).

 

[Flybridge]

Hmmm

I still get an error message

Unable to update the calculated member.
Formula error - cannot find dimension member ("[Periodictiy].&[Normal View")- in a name binding function.

What does that mean?

 

[BKhanna]

Are you sure you created the calculated member in the 'Periodicity' dimension?

 

[BKhanna]

Looks like there was a typo in the name of the dimension. It should be 'Periodicity'. Check the formula to make sure there are no typos.

 

[Flybridge]

Thanks for spotting the obvious :-D

I think I still must be doing something wrong. I can now process the cube but I know get #ERR in the data browser for all measures and views. In Crystal Analysis I get nothing at all.

Is there some other step to perform?

 

[BKhanna]

Did you perform the following steps?

1. Create a dimension table for the Periodicity dimension with one member called 'Normal View'.

2. Add a column to the fact table with the value 'Normal View' in every row of data

3. Create the calculated member in the Periodicity dimension.

If you did all these things, I'm not sure why you get #ERR because mine seems to work fine, and I get the correct numbers. Just go over the steps again to make sure you did the above things.

 

[Flybridge]

Done all that.

What I get is #ERR for any view that is not the Year to Date view in Periodicity. When I do choose that view I get blanks.

Totally confused now :-(

 

[BKhanna]

Can you describe your dimension table to me? Just do a select * from that table so I can see what you have. Also, can you list the steps you followed to create the calculated member?

 

[Flybridge]

Select from Periods dimension:
Columns = [Dim key], [Company], [Division], [Year], [Period], [Period name], [YearPeriod], [From date], [To date], [Number of calendar days], [Number of workdays], [Number of bank days], [_Created_], [_Modified_], [_Status_], [_Session_]

1 625 001 2002 0 2002 200200 0 0 0 0 0 2004-06-07 15:18:27.733 NULL 0 11
2 625 001 2002 1 January 200201 20020101 20020131 31 23 23 2004-06-07 15:18:27.733 NULL 0 11
3 625 001 2002 2 February 200202 20020201 20020228 28 20 20 2004-06-07 15:18:27.733 NULL 0 11
4 625 001 2002 3 March 200203 20020301 20020331 31 21 21 2004-06-07 15:18:27.733 NULL 0 11
5 625 001 2002 4 April 200204 20020401 20020430 30 22 22 2004-06-07 15:18:27.733 NULL 0 11
6 625 001 2002 5 May 200205 20020501 20020531 31 23 23 2004-06-07 15:18:27.733 NULL 0 11
7 625 001 2002 6 June 200206 20020601 20020630 30 20 20 2004-06-07 15:18:27.733 NULL 0 11

I defined the Periodicity dimension in Analysis Manager. I don't know how to run a query over it in AM. Sorry.

And this forum doesn't let me paste in a graphic of the Browse Data. That just has an All Periodicity level with a Normal View below it (with the f symbol to show it is calculated.)

The steps I followed are:
1) in the source data, created a column called Normal View in the fact table and defaulted that to 'Normal View' for all records.
2)In Analysis Manager created the shared dimension Periodicity from that column and attached it to the cube.
3) Edited the cube and created a calculated member, Year to Date View with the formula

Sum( PeriodsToDate(), [Periodicity].&[Normal View]) attached to the Periodicity dimension.

I can't see if I have done anything different to what you have said, but hopefully you will spot it if I have.

Thanks.

 

[BKhanna]

Your period dimension seems to have a lot more than just periods, like company and division, so I'm not sure what you are trying to do. You need to define the period dimension as a time dimension in order to use the period to date function. You can do this in 'Advanced' properties in the dimension editor.

My period dimension is a straightforward time dimension with Quarters, Months and Days only.

Hope this helps.

 

[Flybridge]

Oops.

Sorry, I showed you the table that is the source for the dimension. The Period dimension just has Year (2003, 2004) and Period (1, 2, 3 etc)

It isn't defined as a time dimension because there are no date members as such, just numbers for year and period. I'll try to define it as a time dimension and see what happens.

 

[Flybridge]

Hmmm, just checked and it is defined as a Time dimension. (Don't remember doing that!).

 

[BKhanna]

In one of your posts you mentioned that the Periodicity dimension has a 'Normal View' with an f symbol to show that it is calculated. Not sure what you meant by it, as 'Normal View' is not calculated but loaded from the fact table.

 

[Flybridge]

Ah. Didn't realise you meant for me to load it from the fact table.

Anyway, I found a couple of things on the MSDN web site. One was similar to your technique - and that didn't work either. The other one does give me what I need, although it does involve creating a calculated member for each measure that needs reporting on a year to date basis. However, as that allows me position a period and a YTD value side by side then I'm OK with that. And I don't have that many measures in the cube (it being a finance cube).

Thanks for your interest in this. I will keep playing with your solution to see if I can get it to work for me. I can see instances where that would be a more preferable solution.

 

[BKhanna]

I'm glad something finally worked for you! I know it was driving both of us crazy!