October 9, 2009 at 10:01 am
Hi,
I need to base a calculation on a Grand Total separately for the current month and previous month. Using the time dimension as the page filter, it looks like I can use the ROOT function - (root([Carrier]), [Measures].[BMOU]). Although I haven't added in the rest of the formula this looks like it would work as long as I'm only looking at the Carrier dimension.
My question is how can I tell which dimension is being used in something like a case statement so I could add in the appropriate Root function?
I see this as something like this. Excuse my poor pseudo code as I'm very new to MDX.
Case
When [Carrier].dimension.name Is -- this is not right but I hope you get the idea
"like" Carrier -- this would need to catch any level and hierarchy
Then
(root([Carrier]), [Measures].[BMOU]) * [Measures].[AnotherCalc]
When [Product].dimension.name Is
like Product --
Then
(root([Product]), [Measures].[BMOU]) * [Measures].[AnotherCalc]
Else 0
End
Thank you for any guidance you can provide.
Mark
October 9, 2009 at 2:09 pm
Im not quite clear on what your looking for but here is how I navigate dates for KPI's and stuff
Case When [Date].[Date Time].CurrentMember.Level.Ordinal = 3 Then //month level
{([Measures].[Order Count])- ([Date].[Date Time].Prevmember,[Measures].[Order Count])}
End
October 9, 2009 at 2:37 pm
Thanks for the reply Derek.
I need to use the value of what I see in the Grand Total of a measure in a calculation for any given level or dimension. In this case there are 2 - a Grand Total for July and one for August. So the formula would look something like:
(AugustGrandTotal - JulyGrandTotal) * CalculatedMeasure * AnotherCalculatedMeasure
Does that make sense? I thought the Root function would help but I haven't been able to get it to work for my case. I'll put together a better example to try and clear up the confusion.
Thank you.
October 13, 2009 at 7:47 am
Let's say for example I drop in a measure (call it measure1) into a cube browser and then drop a time dimension (call it time1) into the column field. In this example I only have July and August so now I'm showing a grand total under each month for measure1.
Now I drop in another dimension (dimension2) into the row fields. Obviously this breaks out the numbers in measure1 for each dimension2 category. At the bottom, it still shows the grand totals for measure1 for July and August. I need the value of the grand total for July and August in a calculation. If I filter on dimension2 this grand total changes. I don't want that new value. I only want the grand total for All categories (or attributes excuse my not knowing the right terminology) no matter what gets filtered out.
Is there a way to get these values in a calculation?
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply