20 Replies Latest reply: Jul 13, 2017 7:28 AM by Sunny Talwar RSS

    Create Pareto Bins in Load Script

    Phalgun Parvathaneni

      Hello,

       

      Is there a way to create Pareto bins (say Top 80%,80-95% and Bottom 5%) in the load script?Say my data has Vendor Name and Sales. I'd like to create the above mentioned bins for this data. If you could you please explain with the help of an example. I desperately need help! TIA!

       

      stalwar1 vinieme12. Please help!

        • Re: Create Pareto Bins in Load Script
          Anil Babu Samineni

          Read this? May be helps to you

          Buckets

            • Re: Create Pareto Bins in Load Script
              Phalgun Parvathaneni

              I have read that already but as I'm fairly new to Qlik Sense and don't know the commands used to create Pareto bins in the load script, it'd be helpful if you could give me a detailed recipe to create such bins in the load script. Thanks!

                • Re: Create Pareto Bins in Load Script
                  Anil Babu Samineni

                  Would you provide sample data?

                    • Re: Create Pareto Bins in Load Script
                      Phalgun Parvathaneni

                      Thanks for taking the time!

                      Let's say I have the following data. I'd like to classify them into 3 Pareto bins: Top 80%, 80-95%,Bottom 5%

                      Vendor       Sales

                      A              1300

                      B              2200

                      A              1400

                      B              2100

                      C              2500

                      A              300

                      C              900

                      D              3200

                      B              2000

                      D              1500

                      E              1100

                      F              3500

                      E              1500

                      G              1400

                      H              600

                      H              900

                      G             1200

                        • Re: Create Pareto Bins in Load Script
                          Sunny Talwar

                          May be this

                           

                          Table:

                          LOAD * INLINE [

                              Vendor,      Sales

                              A,              1300

                              B,              2200

                              A,              1400

                              B,              2100

                              C,              2500

                              A,              300

                              C,              900

                              D,              3200

                              B,              2000

                              D,              1500

                              E,              1100

                              F,              3500

                              E,              1500

                              G,              1400

                              H,              600

                              H,              900

                              G,            1200

                          ];

                           

                          Left Join (Table)

                          LOAD Sum(Sales) as TotalSales

                          Resident Table;

                           

                          TempTable:

                          LOAD Vendor,

                            Sum(Sales)/TotalSales as SalesByVendor

                          Resident Table

                          Group By Vendor, TotalSales;

                           

                          Left Join (Table)

                          LOAD Vendor,

                            RangeSum(Peek('CumSalesByVendor'), SalesByVendor) as CumSalesByVendor,

                            If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.80, Dual('Top 80%', 1),

                              If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.95, Dual('80-95%', 2), Dual('Bottom 5%', 3))) as Bucket

                          Resident TempTable

                          Order By SalesByVendor desc;

                           

                          DROP Table TempTable;

                            • Re: Create Pareto Bins in Load Script
                              Anil Babu Samineni

                              You are the boss, How come you handle all stuff with in the Time only. I am trying same.

                              • Re: Create Pareto Bins in Load Script
                                Phalgun Parvathaneni

                                You are the best! Thanks a lot!

                                • Re: Create Pareto Bins in Load Script
                                  Phalgun Parvathaneni

                                  I also have a small clarification if you don't mind. Now let's say I have another field: Type. The data is now as follows:

                                  Vendor       Sales    Type

                                  A              1300        C

                                  B              2200        N

                                  A              1400        N

                                  B              2100        N

                                  C              2500        C

                                  A              300          N

                                  C              900          C

                                  D              3200        N

                                  B              2000        C

                                  D              1500        C

                                  E              1100        N

                                  F              3500        C

                                  E              1500        C

                                  G              1400        N

                                  H              600          C

                                  H              900          N

                                  G             1200         C


                                  Now I'd like to apply a filter on Type. Let's say I apply the filter as 'C'. Now will the buckets be created on fields which only have the type C or will it still be created on all the fields? If it is still on the overall data, how can I make it create buckets only on the fields which have type as 'C'? Is it possible at all?


                                  Thanks a lot again! You are a life saver! Much much appreciated!

                                    • Re: Create Pareto Bins in Load Script
                                      Sunny Talwar

                                      Anything done in the script will be static on the front end... To create the same thing on the front end, you will need thisRecipe for a Pareto Analysis – Revisited

                                        • Re: Create Pareto Bins in Load Script
                                          Phalgun Parvathaneni

                                          Thanks! But the problem is I still need to have the same buckets as my dimension and the Sales are the measures. Could you please give me any idea how I can achieve this? The buckets also need to be done on data selected by filters only as I mentioned above.

                                           

                                          Thanks again!

                                            • Re: Create Pareto Bins in Load Script
                                              Sunny Talwar

                                              Thanks! But the problem is I still need to have the same buckets as my dimension and the Sales are the measures. Could you please give me any idea how I can achieve this? The buckets also need to be done on data selected by filters only as I mentioned above.

                                              Same buckets? regardless of selections buckets need to change or they don't need to change? Selection in type will change the Sum(Sales) and the sorting will change and Top 80%, 80-95%, and Bottom 5% will change. So, technically you should can see new buckets, but I am confused if you want to see new buckets based on selection in type or should it always show based on over sales?

                                                • Re: Create Pareto Bins in Load Script
                                                  Phalgun Parvathaneni

                                                  To make myself more clear, let's say we have the data as follows:

                                                  Vendor       Sales    Type

                                                  A              1300        C

                                                  B              2200        N

                                                  A              1400        N

                                                  B              2100        N

                                                  C              2500        C

                                                  A              300          N

                                                  C              900          C

                                                  D              3200        N

                                                  B              2000        C

                                                  D              1500        C

                                                  E              1100        N

                                                  F              3500        C

                                                  E              1500        C

                                                  G              1400        N

                                                  H              600          C

                                                  H              900          N

                                                  G             1200         C


                                                  Now I have created the Buckets: Top 80%,80-95% and Bottom 5%. Now the output is as follows:

                                                  A,B,C,D,F are Top 80%. E & G are 80-95%. H is Bottom 5%. Now I apply the filter on Type = C. The data set changes to:

                                                  Vendor       Sales    Type

                                                  A              1300        C

                                                  C              2500        C

                                                  C              900          C

                                                  B              2000        C

                                                  D              1500        C

                                                  F              3500        C

                                                  E              1500        C

                                                  H              600          C

                                                  G             1200         C


                                                  I would like to create the same Buckets on this data now: Top 80%, 80-95% and Bottom 5%. I have observed that applying such a filter still retains the original Buckets calculated on the entire dataset. What I want is, if filters are applied, the same buckets be calculated on this filtered dataset.


                                                  Thanks!



                                • Re: Create Pareto Bins in Load Script
                                  Sunny Talwar

                                  Try the attached

                                   

                                  Aggr(

                                      If(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8, Dual('Top 80%', 1),

                                          If(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} total Sales),0,RowNo()))<=0.95, Dual('80-95%', 2),

                                              Dual('Bottom 5%', 3))),

                                      (Vendor,(=Sum({<Vendor>} Sales),Desc))

                                      )