Nested Aggregation

[d44973694]

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

[marcus_sommer]

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]

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!

[marcus_sommer]

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

[rwunderlich]

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