Hi,
I want to write a mdx for calculated field in SSAS 2005. This formula will use PERIODSTODATE function. But my date dimension has two hierarchies and I want to cover two of them in the same formula. Is there a way to detect which hierarchy is used. (Time dimension can be placed on filter axis, column axis or row axis)
Thanks in advance.
Nilgun Celikok
Sorry, this can't be done. You would need to create a calculation for each hierarchy.
The reason is that technically both hierarchies always have a concept of a current member. Even when they are not explicitly mentioned in a query, they always have a context. You can see this visually by creating a query with one hierarchy on the columns and one on the rows. In this situation SSAS wound have no way of figuring out which is the "current" hierarchy.
There is a small advantage in that this makes your measures unabiguous. There is less chance of users reporting conflicting results if you have a "Financial YTD" measure and a "Calendar YTD" measure. If you just had a "YTD" measure and your users are anything like mine, then there will be atleast one person who will pick a date without really thinking about which hierarchy they are using.
|||Hi,
I beleive that this can be done using the SCOPE statement, and redefining the calculation for the different heirarchies.
I've defined this calc to only work at the Month or Week level of the time dimension, all other levels should return a blank as the calculation would not make sense.
Here is my example.
Not sure what the result is when you combine the dimensions, something to test!
CREATE MEMBER CURRENTCUBE.[Comparatives].[Same Time LY]
AS Null,
FORMAT_STRING = "#,#",
VISIBLE = 1;
SCOPE(EXCEPT(Measures.Members, {[Measures].[CustCount], [Measures].[SKUCount]}));
SCOPE([Time].[by Month].[Month].Members);
([Comparatives].[Same Time LY]=([Time].[by Month].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
SCOPE([Time].[by Week].[Week].Members);
([Comparatives].[Same Time LY]=([Time].[by Week].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
END SCOPE;
|||It depends what Nilgun meant by "different Hierarchies". I am assuming that because he mentions using PeriodsToDate() he has a configuration with 2 multi-level hierarchies like a calendar and financial hierarchy. This would mean that a given date would have a different set of ancestors depending on which hierarchy we were talking about.
Your example of calculating attribute members differently using scope statements would work if this was the situation Nilgun was attempting to describe.
PS. If you put weeks and months on the rows and columns, my guess is that the week calculation would win as it is last in the calc script, but in the normal course of things there is not direct relationship, so picking a month does not imply setting an particular week. Where as with the financial, calendar hierarchy example, selecting "August 2007" makes it the current member in both hierarchies as there is only the one month member.
|||I have one DATE dimension that has two hierachies.
First one called HDATE1 has following levels
.Year
..Month
...Day
Second one called HDATE2 has following levels
.Year
..Week
...Day
I want to detect if HDATE1 or HDATE2 is used.|||If this is your situation then Bernaud's approach would work. I was concerned that you had different year attributes.
For the Days, it does not matter which hierarchy you use to do the periodsToDate() calc. YTD at August 30 is the same whether you use the week or month. Then you just need different scopes for the week and month attributes, similar to Bernaud's example, but using the PeriodsToDate() function instead of a lag.
