4 Replies Latest reply: Sep 27, 2017 7:06 PM by Rob Wunderlich RSS

    Nested Aggregation

    Deepa Shrivastava

      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:



      Count(Distinct {<[Customer ID]={"=Count(Distinct Product)=1"} >} [Customer ID] )


      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!


        • Re: Nested Aggregation
          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

            • Re: Nested Aggregation
              Deepa Shrivastava

              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?



                • Re: Nested Aggregation
                  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

              • Re: Nested Aggregation
                Rob Wunderlich

                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.