Page 1 of 1
Getting DISTINCT Count using Rules
Posted: Wed Dec 09, 2009 7:21 pm
by ExApplix
I have many dimensions in my cube like Customer, Region, Products, Period, Measures etc. I want to get the
count of the customers who have bought a certain product.
I have added a measure 'Count' and have added this rule
Code: Select all
['Count']=N:IF(['Sales']>0,1,STET);
where 'Sales' is a measure.
This gives me the total count, but the problem is that one customer might have bought a single multiple times (and data source will have multiple transactions). It give me output something like this:
PRODUCT SALES COUNT
Customer A Product1 30 50
Customer A Product1 20 50
Customer B Product3 10 10
Customer C Product4 0 0
But I only want to see how many Customers bought a certain product NOT how many times they bought this product. For example:
PRODUCT SALES COUNT
Customer A Product1 30 1
Customer A Product1 20 1
Customer B Product3 10 1
Customer C Product4 0 0
Please suggest me some solution ASAP. Thanks
Re: Getting count using Rules
Posted: Thu Dec 10, 2009 6:47 am
by Steve Rowe
You may get what you want by removing the N: qualifier from the rule?
I'm guessing that you don't want the count to sum along the region dimension but you do along the other dims?
['Count']=
If ( Ellev('Product', !Product)=0 & Ellev('Customer', !Customer)=0 & & REPEAT THIS TEST FOR ALL DIMS IN THE CUBE EXCEPT MEASURE AND REGION
IF(['Sales']>0,1,STET),
STET);
HTH
Re: Getting count using Rules
Posted: Thu Dec 10, 2009 6:39 pm
by ExApplix
Steve the suggested rule gives me the Count of the Transactions. I actually want to see the DISTINCT Count. I want the rule to set Count=1 IF there are 1 or more than one Transactions.
Both Count and Sales are in Measures dimension.
Please advice the solution. Your prompt reply will be highly appreciated.
Re: Getting DISTINCT Count using Rules
Posted: Thu Dec 10, 2009 7:38 pm
by Michel Zijlema
Hi,
I agree with Steve that you should not restrict the rule to N-level.
If you want a distinct count of the number of customers for a certain product in a certain period in a certain region, etc., I would think something like:
['Count'] =
If(ElLev('Customer', !Customer) = 0 & ElLev('Product', !Product) = 0,
IF(['Sales'] > 0, 1, 0),
STET);
would do?
If you check 'All customers' for the given product, region, etc., doesn't this give you the total number of distinct customers in the selected region, etc. who bought the selected product?
Michel
Re: Getting DISTINCT Count using Rules
Posted: Thu Dec 10, 2009 9:32 pm
by ExApplix
Your proposed solution is giving me the COUNT...but I want
DISTINCT COUNT for the Customer. I want to see how many DISTINCT customer has done transactions i.e if a customer has made multiple transactions my Count should only show 1.
I have added the following rule which gives me the correct DISTINCT count for the N-level elements but its is not showing me the consolidated value of the Distinct counts i.e. the 'All Customers' count value is not correct
Code: Select all
['Count']=IF((['Sales'])>0 & ELLEV('Customer',!Customer)=0,1,STET)
Please advice.
Re: Getting DISTINCT Count using Rules
Posted: Thu Dec 10, 2009 9:37 pm
by Steve Rowe
Hi ExApplix,
So your should remove the Ellev test on Customer.
Also try and bear in mind that we are giving up our free time to help you, what you want to do is not that tricky. We understand the problem and are doing our best to help you.
Cheers,
Re: Getting DISTINCT Count using Rules
Posted: Thu Dec 10, 2009 10:40 pm
by lotsaram
Another viable option would be to create a separate cube without any time dimensions and calculate the active customers in that cube. This might make the rules simpler for you. If you then want to drill back to your main cube and see when and how many transactions there are per customer that would be fairly trivial too.
Re: Getting DISTINCT Count using Rules
Posted: Fri Dec 11, 2009 1:40 am
by Martin Ryan
I'm not sure that Steve's method will give you the correct result at the 'All Customers' level. It will give you 1 instead of a sum of all the customers who have bought items.
Lotsaram's option might work for you. Another option is to create a dummy product called no product as a child of 'All Products' then write a rule
Code: Select all
['No Product', 'Count'] = N: if(['All Products', 'Sales'] > 0, 1, 0);
This will give you a 1 per customer regardless of how many products they've bought, however it will give you a 1 for each day/month/year, whatever you have as time, so you may like to further refine to
Code: Select all
['1', 'Jan', 'No Product', 'Count'] = N: if(['All Products', 'Sales', 'All days', 'All Months']>0, 1,0);
Cheers,
Martin
Re: Getting DISTINCT Count using Rules
Posted: Fri Dec 11, 2009 6:49 am
by Steve Rowe
I was also wondering if the problem was that the feeder was not written hence the wrong result at the prodcut level? When you say the All Customer total is wrong, howis it wrong?
Re: Getting DISTINCT Count using Rules
Posted: Fri Dec 11, 2009 9:15 pm
by ExApplix
I have got that working and now getting the Distinct Counts within the rule (without using any lookup cube).
Code: Select all
SKIPCHECK;
['All Customers','Count'] = ConsolidateChildren('Customer');
['Count']=IF((['Sales'])>0 & ELLEV('Customer',!Customer)=0,1,STET);
FEEDERS;
['Sales' ]=> ['Count'];
Thanks everyone who answered to this post and gave me free time to solve this.
Re: Getting DISTINCT Count using Rules
Posted: Thu Jun 27, 2013 5:21 am
by kaazimraza
ExApplix wrote:I have got that working and now getting the Distinct Counts within the rule (without using any lookup cube).
Code: Select all
SKIPCHECK;
['All Customers','Count'] = ConsolidateChildren('Customer');
['Count']=IF((['Sales'])>0 & ELLEV('Customer',!Customer)=0,1,STET);
FEEDERS;
['Sales' ]=> ['Count'];
Thanks everyone who answered to this post and gave me free time to solve this.
Hi ExApplix,
Have you ever had any performance issue with this ? I have adapted your rule to generate a distinct count of customers. when users try to access the view with counts, the CPU frequently hits 70% to 80%. To give you some background, my sales cube has around 9-10 dimensions. Within the dimensions, Customer dimension has a little over 13,000 elements, and period too has over 4000 elements, and product dimension has up to 800 elements.
Edit:
TM1 version is 9.5.2.
Re: Getting DISTINCT Count using Rules
Posted: Thu Jun 27, 2013 1:04 pm
by Wim Gielis
kaazimraza
While ConsolidateChildren might give you the correct result, and while that function may be needed to get there,
that function is known to be very inefficient compared to standard consolidation algorythms in TM1.
In the bigger cubes (as you seem to have) no doubt performance issues will come up.
Try to go the Turbo Integrator way if possible.
Re: Getting DISTINCT Count using Rules
Posted: Fri Jun 28, 2013 4:31 am
by kaazimraza
Wim Gielis wrote:kaazimraza
While ConsolidateChildren might give you the correct result, and while that function may be needed to get there,
that function is known to be very inefficient compared to standard consolidation algorythms in TM1.
In the bigger cubes (as you seem to have) no doubt performance issues will come up.
Try to go the Turbo Integrator way if possible.
Hi Wim
Thanks for the reply. Before doing it from the TI, do you reckon there's any chance of cracking it through Rules using the CONSOLIDATEDCOUNTUNIQUE ?
Re: Getting DISTINCT Count using Rules
Posted: Fri Jun 28, 2013 7:33 am
by Wim Gielis
That could be good solution too. I used the consolidatedcount function before, but not the one you mention.
Keep us informed on the performance and if you get it to work.
Wim
Re: Getting DISTINCT Count using Rules
Posted: Mon Jul 01, 2013 4:19 pm
by kaazimraza
Hi all, I tried with ConsolidatedCountUnique function, and while I can see performance gains, I am struggling with a correct application of this function. My users would want to see unique counts of customers from different angles, virtually slicing by every dimension available ( all customers for a given Period (day, month, year), product, volume groups etc.) and I have about 8-9 dims in our sales cube. I have tried a couple combinations of consolidatedcountunique and both combos are giving me different values, so now i am lost...
Here's my rule
[ 'Count'] = C: ConsolidatedCountUnique(0, '', 'Customer', !Customer, !Period, 'Tonnes' ) ; (Counting to 580)
[ 'Count'] = C: ConsolidatedCountUnique(0, '', 'Customer', !Customer, !AgreementGroup, 'Tonnes' ) ; ( counting to 800)
Any further tips would be highly appreciated. Thanks
Re: Getting DISTINCT Count using Rules
Posted: Mon Dec 16, 2013 8:07 am
by kaazimraza
Hi all,
A quick update on this one. I had initially implemented distinct counts using ConsolidateChildren function. But as the data grew, it kept on taking more time until ( couple weeks back) it was taking forever to return any results.
I eventually replaced it with ConsolidatedCountUnique
http://www-01.ibm.com/support/docview.w ... wg21472749function. Now the logic to calculate distinct counts lives partly in the TI1 that loads the data, where I keep on assigning 1 to the count measure for every valid intersection/match, while the above function in the rule gets the result, and so far, it's giving pretty positive results.
Thanks,
Kaz
Re: Getting DISTINCT Count using Rules
Posted: Mon Dec 16, 2013 11:25 pm
by Wim Gielis
Hi, thank you for the feedback.