Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

d44973694
New Contributor II

Nested Aggregation

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

4 Replies

Re: Nested Aggregation

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

d44973694
New Contributor II

Re: Nested Aggregation

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!

Re: Nested Aggregation

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

Re: Nested Aggregation

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

http://masterssummit.com

http://qlikviewcookbook.com