puzzled by measures

[blom0344]

I have created some cubes for offline use. These deal with sales. One of the dimensiontables stores time dimensions.

If salesdata belongs to the year-to-date group then I have a value in the time table that reads 'JTDCY'

So, for margin I created a measure:

(Sum({JTDCY},(Measures.Amount - Measures.Cost))/Sum({JTDCY},(Measures.Amount)))

This seems to work, but:

How does analysis services evaluate to which dimension 'JTDCY' belongs?
And why in YTDCY not allowed as a value in the Time-table?

In short, how does this work anyhow?

Ties Blom
Information analyst

 

[MDXer]

what is the MDX for JTDCY?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[blom0344]

???

JTDCY is a value from the time-dimensiontable
In this case it means ' Year-to-date current year' and it is assigned to every date of 2005 up till the systemdate.

This time table is refreshed every day to be able to use it for YTD calculations.

Initially I used the value 'YTDCY' in the time table , but it did not parse when building the calculated measure.
Out of the blue , I replaced the 'Y' with the 'J' (dutch: jaar) and the value was accepted in the calculated measure.



Ties Blom
Information analyst

 

[Flybridge]

I don't understand quite what you are saying. Year to Date is normally part of the definition of the calculated member (using the PeriodsToDate function) not a member of a time dimension.


'The world isn't round - it's bent!' Spike Milligan

 

[blom0344]

Well, with your suggestion, how would the expression be then?
I have a 'relational' background and I am used to solving time-issues with time-dimension tables.
Furthermore I need to compare year-to-date data over a range of years.
How would you create calculated members that show SalesYTD2005,SalesYTD2004,SalesYTD2003 as SEPERATE objects in the Cube.
This last requirement is something I cannot avoid

Ties Blom
Information analyst