Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikies,
We are trying to find out number of customer for whom number of products is 1, or greater than 1
Expression we are using is:
Expression1
Count(Distinct {<[Customer ID]={"=Count(Distinct Product)=1"} >} [Customer ID] )
Expression2
Count(Distinct {<[Customer ID]={"=Count(Distinct Product)>1"} >} [Customer ID] )
We are plotting these expressions with product as dimension. So for product A if # of customer is 50 of which 20 are only associated to this product, and 30 with other products too, we will have a stacked bar for this product with stacks of 20, and 30
These expressions yield correct results but we are running into performance issues. Can someone please suggest a better solution to this?
Thanks in advance!
Deepak
It's rather difficult to improve the performance of the expressions. Perhaps you could find a way to avoid the Distinct, maybe with something like:
sum({<[Customer ID]={"=Count(Distinct Product)=1"} >} 1)
If this didn't work or perform much better you will need to transfer (parts of) this calculation into the script. Also the used datamodel might be worth to changed - if for example both fields belong to different tables maybe even connected over a link-table it would cost a lot of performance and a star-scheme or a big flat-table would be more suitable.
- Marcus
We can't remove the distinct count as that would compromise our requirement. However we can try bringing both Customer, and Product in a single table if possible.
Just to confirm, can we utilize P() function in this case?
Thanks!
I don't think that p() could be useful in this case. Maybe there are possibilities to replace the distinct product-count with something similar:
Count(Distinct {<[Sales]={"*"} >} [Customer ID] )
It won't fit with your check to exact one product or more than one product - the idea behind this suggestion is to check the condition with other fields.
- Marcus
Depending on your data model, this may run faster:
-sum(aggr(Count(Distinct Product ), [Customer ID] )>1)
As Marcus suggested, you may also be able to do some calculation in the script.
-Rob