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

    Nested Aggregation

    Deepak 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:

       

      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

        • 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
              Deepak 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?

               

              Thanks!

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

                 

                -Rob

                http://masterssummit.com

                http://qlikviewcookbook.com