6 Replies Latest reply: Jun 15, 2016 5:37 AM by barry WIlmer RSS

    Distribution Question

    barry WIlmer





      I am new to Qlik, so please be gentle with me!


      I have a file of data, about 70K lines. Each line is a transaction with a value in €

      I want to show the distribution of the number of orders relative to the value (80:20 Rule)

      I can do this in Excel using Frequency array formula to get the number of orders for each bin, and sumif( to get the value for each bin.

      This gives the two lines I need to show (see graph)

      My question is simple, but I suspect the answer may not be, how can I do this in Qlik Sense from the data? I have tried various ways but all fail ~ can anyone help





      14-06-2016 08-41-15.jpg

        • Re: Distribution Question
          Sunny Talwar

          Would you be able to share the excel file here and may be let us know which of the columns need to be calculated in Qlik Sense?

          • Re: Distribution Question
            barry WIlmer


            The columns I want to plot is the Pink (Num Cum %) and Green (Value Cum %)

            The chart I want  is shown in my original post


            enclosed is a sample of the data, it is a list of values

            thanks for your help


              • Re: Distribution Question
                Sunny Talwar

                Can you post the data in Excel format? Images require me to manually enter all the data into QlikView. If you need directions on how to upload files to community, check this out -> Uploading a Sample

                  • Re: Distribution Question
                    barry WIlmer

                    Sorry here is a sample of my data



                      • Re: Distribution Question
                        Sunny Talwar

                        May be like this. Script:



                        LOAD [Working Price],

                          If([Working Price] <= 100, Dual('<=100', 1),

                          If([Working Price] <= 500, Dual('<=500', 2),

                          If([Working Price] <= 1000, Dual('<=1000', 3),

                          If([Working Price] <= 3000, Dual('<=3000', 4),

                          If([Working Price] <= 5000, Dual('<=5000', 5),

                          If([Working Price] <= 10000, Dual('<=10000', 6),

                          If([Working Price] <= 15000, Dual('<=15000', 7),

                          If([Working Price] <= 20000, Dual('<=20000', 8),

                          If([Working Price] <= 25000, Dual('<=25000', 9),

                          If([Working Price] <= 50000, Dual('<=50000', 10),

                          If([Working Price] <= 75000, Dual('<=75000', 11),

                          If([Working Price] <= 100000, Dual('<=100000', 12),

                          If([Working Price] <= 250000, Dual('<=250000', 13),

                          If([Working Price] <= 500000, Dual('<=500000', 14),

                          If([Working Price] <= 1000000, Dual('<=1000000', 15),

                          If([Working Price] <= 2000000, Dual('<=2000000', 16),

                          If([Working Price] <= 5000000, Dual('<=5000000', 17),

                          If([Working Price] <= 10000000, Dual('<=10000000', 18),

                          If([Working Price] <= 25000000, Dual('<=25000000', 19)))))))))))))))))))) as Bin


                        [sample data.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                        Line chart






                        1) =RangeSum(Above(Sum([Working Price]), 0, RowNo()))/Sum(TOTAL [Working Price])

                        2) =RangeSum(Above(Count([Working Price]), 0, RowNo()))/Count(TOTAL [Working Price])




                        I calculated Bin in the script, but can be done dynamically on the front end as well.