6 Replies Latest reply: Dec 27, 2017 8:48 AM by Doug Kamen RSS

    Set Analysis on aggr totals

    Doug Kamen

      I'm trying to see if their is a Set Analysis way to solve the following problem.  I used an if statement.

       

      Problem

      For each "Customer", show the "Total Revenue" if they have order at least 100 units (Quantity) in all their orders combined.  There are multiple orders per customer and I only care if they exceed 100 combined.

       

      Current Solution

      if(aggr(sum(Quantity), Customer) > 100, Total Revenue, 0)

       

      Is there a set analysis way to do this?  Should it be done with set analysis?

        • Re: Set Analysis on aggr totals
          Luis Madriz

          Hi,

           

          Do you want to try this for Revenue: =sum( {$ <Customer={"=sum(Quantity)>100"} >} Revenue)

           

          Please let me know,

           

          Cheers,

           

          Luis

            • Re: Set Analysis on aggr totals
              Doug Kamen

              Good solution to the problem above. However, in analyzing the data, there was a wrinkle.  When I grouped by Region, the filter seemed to apply to the entire data set, rather than being limited to the Customer/Region.

               

              How would I solve the following where Region is added to the aggregation using Set Analysis?


              if(aggr(sum(Quantity), Customer, Region) > 100, Total Revenue, 0)

                • Re: Set Analysis on aggr totals
                  DANIEL CHOTZEN

                  hmm, i might be mistaken but i'm not sure that its possible,

                  set analysis is calculated once per the entire object so it is unaware of the dimensions in it, only after the set analysis calculates which rows are possible for the chart, then the object calculates in accordance with the dimension values.

                  so you will need some form of aggragation in order to make the sum of 100 the limit of each customer and region.

                  probably the IF is only option

                   

                  Wizardo

                  • Re: Set Analysis on aggr totals
                    Sunny Talwar

                    You can make a new field in the script like this

                     

                    LOAD Customer,

                         Region,

                         AutoNumber(Customer&Region) as CustomerRegionKey

                         ...

                    FROM ....;

                     

                    and then this

                     

                    Sum({<CustomerRegionKey = {"=Sum(Quantity) > 100"}>}[Total Revenue])

                    • Re: Set Analysis on aggr totals
                      Luis Madriz

                      Hi,

                       

                      I'm back now. Hope you guys had a nice break!

                       

                      Doug, I've been learning Qlik for the last few months and I'm still struggling but amazed by the power of Qlik associative model, Scripting and Set analysis compared to SQL and other languages... but I'm still struggling:

                      I put together the following data:

                      Untitled.png

                       

                      The first thing I would've done is this:

                      =Sum(IF(AGGR(sum(Orders),Customer,Region)>=100,Revenue))

                       

                      Which got me this:

                      Untitled.png

                       

                      Which is not correct because is not adding up all the occurrences that I was expecting

                       

                      This could have been done by using just using Limitations on the the dimensions based on Sum(Orders) -or Quantity in your design-, but with the inconvenience that it's required to display that measure... so it's sort of like cheating...

                      Untitled.png

                       

                      So, after continuing to struggle for a while I would've gone back to the first document I read on set analysis (attached) to confirm that I needed to find all the members that met the condition which is something like this:

                      =Sum({<Region&Customer = {"=Sum(Orders)>=100"}>}Revenue)    which is not the correct syntax so I needed a new single field that would give me each individual combination of Region and Customer (members)

                      load *,

                           Region&'-'&Customer as RegionCustomer;

                      load * inline [

                      Region,Customer,Orders,Revenue

                      A,1,20,89

                      A,2,30,90

                      A,3,40,91...

                      Untitled.png

                       

                       

                      And of course, instead of wasting performance with that concatenation, the best option is what Sunny proposed!

                      load *,

                           AutoNumber(Region&'-'&Customer) as RegionCustomerKey;

                      load * inline [

                      Region,Customer,Orders,Revenue

                      A,1,20,89

                      A,2,30,90

                      A,3,40,91

                       

                      Untitled.png

                       

                      I hope this helps,

                       

                      Cheers,

                       

                      Luis