asked
problem with mdx grand total
hi , i'm using calculations in olap cube i wrote this query (mdx) but i don't get the grand total of "orders" when i use pivot table . case when([Measures].[orders]>0)then 1 else null end thanks erez
Ahhh I see! right ok, you'll need to do something like the below, assuming you have a dimension called retailers and a dimension attribute called Retailer Name: COUNT(FILTER([Retailers].[Retailer Name], [Measures].[orders]>0)) This will give you the desired result. Alternatively I think you could even modify your original attempt to something like this and it work: SUM([Retailers].CurrentMember,CASE WHEN[Measures].[orders]>0)THEN 1 ELSE 0 END)
Hi Erez, are you saying you've created this as a calculated measure in the cube? What are you trying to achieve with this query? If you're taking a sum over this calculated measure, what you're actually doing is a COUNT of the number of measures that are > 0. Is this what you intended?
Hi Dave , yes , I created a calculated measure in the cube there is a hierarchies of customers that build like this area Wholesaler Retail i want to show the number of retailers who bought a selected item in each level. right now i only get the number 1 neer every cutomer who bought the item . in this case sum and count will get the same number becuase every retailer get the number 1 , even if he bought more then one time.
Ok so if I understand correctly you need to put a SUM around this calculation in order to get what you require. This is providing you have set up all your dimension relationships correctly between the fact. The other approach is to use the COUNT() and NONEMPTY() functions something akin to: (Psudo code) COUNT(NONEMPTY(, [Measures].[orders]))
Ah ok, try dropping the case statement : COUNT (NONEMPTY(,[Measures].[orders] ))
hi i use COUNT (NONEMPTY(,[Measures].[orders] )) but i still get #VALUE! in all the parameters it's importent to say that "orders" is alsow a culculate that give number of Buy back if the Retail bought 1 time he will get 0 if the Retail bought 2 time he will get 1 if the Retail bought 3 time he will get 2 and eatra...
I'm sorry I'm just not following your requirement, you keep adding these seemingly important caveats. Can you please give a full requirement of exactly what you're trying to achieve
O.K there is 2 calculations in the olap cube 1. The amount of repeat orders - this one is good (if the number is more then 0 ,it means that the Retail bought the product more then one time ) 2. Number of retailers who reorder - who Relies on the first calculate (the retailers that got number that big then 0 ) I'm trying to show in each level the sum of the retailers who bought more then one time the product . i hope that i'm more clearly . thanks again. i'm trying
9 People are following this question.
