Getting DISTINCT Count using Rules

[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

[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

[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.

[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

[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.

[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,

[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.

[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

[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?

[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.

[kaazimraza]

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.

[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.

[kaazimraza]

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 ?

[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

[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

[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

[Wim Gielis]

Hi, thank you for the feedback.