Measure Count where CustID is -1
-
I have changed the underlying database so all NULLs are now -1 with a corresponding dimension of -1 as -- Not Known --
But now i am having trouble doing the counts I want.
How do I create a measure where I can:
get a count of all CustID = -1 in my FACT Table
get a count of all CustID <> -1 in my FACT Table
I am assuming the only way to do this is with a calculated member. I can write SQL to do it in a db but I am having trouble with the MDX(?)
It was much easier when I left the NULLs in. Then i just did a count on the CUSTID
Thanks in advance
-
I created a case statement in the FACT table DSV that checked for "-1"
CASE CUST_ID WHEN '-1' THEN 1 ELSE 0 END AS Count_Other,
CASE CUST_ID WHEN '-1' THEN 0 ELSE 1 END AS Count_Cust
Then i created 2 measure to SUM on Count_Other, Count_Cust.
Works, HOWEVER i am very disappointed i couldnt do this with calculated members.
How is everyone else doing there counts for this common situation?
-
Sure... I think you could do it with MDX by using the Descendants function to look at the lowest level values in combination with IIF to determine if the value is -1 or not.
-
I think part of the problem you'll run in to is that the count of all customers in your fact where the cust_id was NULL and is now -1 is exactly .... 1 (one). They'll all group to be a single 'unknown' customer. I guess in some ways this is correct, if you kept them as nulls, you'd still have to count something else (invoice_id??) to get the transaction count, either way, you'll not get a true customer count because being NULL or -1, how could you tell whether it's the same 'not registered' customer buying something 200 times or 200 individual customers?? You can't count something you're not collecting, right?
Steve.
-
I may be thinking about this wrong as my brain is fried from a long week. But potentially, you could add Customer_ID as a measure in the cube and then create a calulated member on top of that to show what you want. So going back to what I said before, looking at the lowest level with the descendants function and applying IIF logic to sum only the -1 values (with ABS to get a positive value) should give you an accurate count.
-
I think i'm struggling this Friday also
Agreed you can definitely count the -1's, either from the fact or the dim. The dim count will give you a count of 1 (ie there's only 1 record with the PK of -1). Counting the -1's in the fact will give you more of a 'transaction count where i didn't know the cust_id', so unless the intention is to know how many transactions there were where we didn't capture a customer_id then i'm not sure how you'd get an accurate 'unknown customer' count because the same 'unknown' customer may have transacted more than once. I guess i'm saying you're counting transactions not customers when counting the -1's in the cust_id field in the fact - each -1 doesnot necessarily represent a unique customer (and if it did, we should have recorded a cust_id for each one!)
Steve.
-
The best way to deal with this problem, is to deal with / create facts that'll aggregate easily during cube processing:
Create a count of your "data" records:
Count Data --> AggregateFunction = Count
Source = DataTable(Row)
Create a count field, counting the most useful case (probably "defined sales"):
In the DSV: CASE CUST_ID WHEN '-1' THEN 0 ELSE 1 END AS Count_Cust
Count Cust --> AggregateFuntion = Sum
Source = Count_Cust
Now create a calculated member for the negative case:
Count No Cust = [Count Data] - [Count Cust]
NB. You can also get customer specific counts and by extension a count of the "-1", using the DISTINCT COUNT function. Careful, since it may not scale well over large data sets.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply