Hi Everyone,
i have a question concerning dimensional functions (e.g. in a dmr modell).
Please imagine a crosstab, the rows are facts, the columns some prompt based filtered dimensions.
D_Time D_Region .....
Fact 1
Fact 2
Fact 3
Time e.g. is filtered by 2 prompts, one for month and one for year, so i can filter the column for a mont in a special year. IM using the function
"currentmeasure within set(filter([D_time].[year]; [D_Time].[year] = ?year?)
Works well so far...
Now i want to create a calculation, for example
(Fact 1 filterered for year 2012) - (fact 1 filtered for year 2011) / fact 1 (filtered for year 2012 AND Region)
=> So i want to use several filter (and dimensions) in one expression...is that possible with the normal filter function? Do i have to create a 'case-when' Statement like
case region
when usa then (Fact 1 filterered for year 2012) - (fact 1 filtered for year 2011) / fact 1 (filtered for year 2012 AND Region)
I hope there is (an easy) way to solve that challenge..,
any ideas are welcome, thx and cheerz :P
What you're looking for is the value at an intersection or a tuple.
For example you could implement your calculation as:
tuple( [Fact1], [2012] ) - tuple( [Fact1], [2011] ) / tuple( [Fact1], [2012], [Region X] )
The arguments to the tuple function are members from different hierarchies. You could prompt for the year member if you want. E.g. [D_time].[year]->?year? and then use a relative time function to get the previous year member.
Hope that helps.
Hi Greg,
ty for the fast reply :)
I have tried the tuple functions as well, but as far as i know / learned the tuple function just gives you (as you already mentioned) an intersection.
When i say i need to create a calculatoin, i mean a new COLUMN for all measures in the rows...so i really need to find a way to filter the set with more then 1 filter expression in order to achieve my goal.
The worst case would be to set the cellsproperty to "define content = Yes", then create singletons and use a tuple function for every single fact...but besides the fear of a bad report performance i also dont want to make this effort, because there are several (bout 25) facts...
Still, ty, and further ideas are welcome :)
Why can't you just add a Query Calculation as a column in the crosstab and then use currentMeasure in the tuple functions?
Maybe it would help if I could see a mockup of what you're looking for with sample data.
hi,
sry for the late reply. it worked the way you guys suggested. thx a lot for helping out and have a nice day :)
cheerz