January 22, 2002 at 6:48 am
I have a cube with my own "product sales" on a daily basis and another cube that contains "total sales" on a daily basis too. By using a common date dimension I connected the cubes into a virtual one. Now, I can calculate the Market Share of my products by applying the formula "product sales"/"Market Sales". This works fine when I have filters only on the common Time Dimension. If, however, I filter on the product dimension, the formula fails as there is no data in the relevant cross section.
How can I state that instead on using "total sales" in the formula, the system should use the "total sales" amount of the Time.CurrentMember?
At the moment the definition is as follows and it returns 0 when intersecting with other dimensions than Time
IIF(IsEmpty(([Measures].[Total],[Date].Currentmember)),0,([Measures].[Amount]/([Measures].[Total],[Date].Currentmember)))
Thank you in advance for any help!!!
January 24, 2002 at 11:34 am
If I understand your question, I think the problem is that the only intersection between your total Market Sales and your product dimension is at the top of the dimension. In MDX, if you do not define a specific member from a dimension, it always assumes the current member. That would mean that your formula is dividing your own sales for your product filter selection by Total sales for the same product selection. If this is the case, you would need to add the Product dimension to your tuple with something like this....
IIF(IsEmpty(([Measures].[Total],[Date].Currentmember)),0,([Measures].[Amount])/([Measures].[Total],[Products].[All Products],[Date].Currentmember)))
Also, be sure each tuple is enclosed in (). Hope this helps.
January 28, 2002 at 12:36 am
I believe I found the solution.
I wanted the Market Share to be calculated based on the common Time dimension. So, I did not create any virtual cube but instead modified the measure in the "Product Sales" cube as
[Measures].[Amount]/lookupcube("Total","([Measures].[Total],"+[Date].Currentmember.UniqueName+")")
and this way it gets the Market Total from the "Total" cube based on the selected Time Dimension member.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply