10 Replies Latest reply: Nov 25, 2010 5:01 AM by Wouter Hollander RSS

    Decile Analysis

    davissiew

      Hi all,

      Does anyone ever did a decile analysis before? In case you do not know decile, its something like Pareto Select but is in 10 portion instead of 2.

      Decile analysis splits eg. users into 10 evenly numbered groups, which Pareto analysis splits the top 20% from the bottom 80%.

      Currently I'm using slider to let user choose each decile using coloring in a straight table chart. Another button to trigger macro using GetCell to loop this table checking the range applicable and add into array, subsequently flush the array into Current Selection. This getcell method really is time-consuming. User can then analyze base on Current Selection. But this only analyze each decile at a time, in future I'd need to see the decile in totality, means to see all the 10 portions at 1 glance (eg. trellis) which I do not know any way to do it.

      Anyone has any idea on my GetCell method above which is very time-consuming, imagine I have 10mil of users in that table.

      Any expert can assist on the decile in totality? Perhaps using set analysis or manipulating Pareto? 1st off, I'll try use Pareto to manipulate but really appreciate the expert out there to help and input ideas...thx in advance.

      Davis

        • Decile Analysis

          Hi Davis,

          try to use "Fractile". See the attached example for more information.

          Good luck!

          Rainer

            • Decile Analysis
              davissiew

              Hi Rainer,

              Thx for the sample, but however I do not quite understand how the aggr function works (even looking at the Help), meaning I'm not able to get quartile-1 figure of 32625 or other quartile's figure. Do you mind giving me the formula its using or using simple 1 by 1 expression to get the figures?

            • Decile Analysis

              Good morning Davis,

              in this example we want to have the sales figures for the whole companys.

              That´s the reason we need "AGGR" for aggregation of the single values.

              Hope it´s a bit clearer now.

              Otherwise upload an QV example and I´m sure that someone in the forum are able to help you.

              Good luck!

              Rainer

                • Decile Analysis
                  davissiew

                  Hi Rainer,

                  Sorry for late reply, was away for weeks.

                  I've attached a sample which I currently work on for Decile Analysis.

                  I'm hoping to press Go button on any of the slider and the selection of Names is pass into "Current Selection" fast, preferably using qlikview in-built feature, not macro. Because my current macro (using GetCell method to loop my table listing to compare) approach will hit performance issue if no.of row or selection is huge. Limitation of this sample is only able to select 1 tiles at 1 time.

                  My attached is Sampling of Total Names : 9000 and total APE : 118,987,504.03, sorted by APE descending.

                  Our Decile Analysis formula :

                  Slider 1 : Rank - Categorize No.of Names into 10 tiles (from 1st 900 Names each tile)

                  Slider 2 : APE Cummulation - Categorize No.of Names with APE Cumm. into 10 tiles (from 1st 11,898,750 (round-down) APE each tiles)

                  Slider 3 : APE Cumm % - Categorize No.of Names with APE % Cumm. into 10 tiles (from 1st 10% of APE % Cumm each tiles)

                  But ultimately, my user requested decile in totality which I do not have any idea on how to go about it. Meaning Rank/APE Cummulation/APE Cummulation % will be dimension, user can select each 1 or more tiles to analyze or show in totality (10 tiles).

                  Hope anyone has an idea or suggestions.

                  Davis

                    • Decile Analysis
                      davissiew

                      Just tried out another method using aggr function, which i put at calculated dimension. Its working for my Decile by Ranking..

                      =if(aggr(ceil(rank((sum(APE) ),4)),Name) <=vDecileT1,'d1',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB2 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT2,'d2',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB3 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT3,'d3',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB4 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT4,'d4',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB5 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT5,'d5',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB6 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT6,'d6',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB7 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT7,'d7',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB8 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT8,'d8',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB9 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT9,'d9',
                      if(aggr(ceil(rank((sum(APE) ),4)),Name)>vDecileB10 and aggr(ceil(rank((sum(APE) ),4)),Name)<=vDecileT10,'d10'))))))))))

                       

                      But now working on Decile by APE Cummulation and Percentage, with no luck. Appreciate if anyone can suggest, thx.

                        • Decile Analysis
                          davissiew

                          I have a formula for APE Cumm and % : if(sum(aggr(rangesum(top(sum(APE),1,rowno()))/rangesum(top(sum(APE),1,noofrows())),Name)) <= .1,sum(APE)) and so on to get 10 tiles. But unfortunately I can't apply it into calculated dimension, adopting my previous successful Decile by Ranking approach. I suspect calculated dimension do not recognize RangeSum syntax. Using this formula in chart expression, need to have 10 charts each and specifying the % at each chart and the performance is like ages. Took too long to up the charts.

                          Appreciate it any expert can contribute ideas..Thx..

                            • Decile Analysis
                              davissiew

                              Anyone knows how to sort or rank my list when using below calculated dimension :

                               

                              =if(aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop1,'Top',

                              if(aggr(rangesum(above(sum(APE),0,rowno())),Name)> vDecileTop1 and aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop2,'D-2',

                              if(aggr(rangesum(above(sum(APE),0,rowno())),Name)> vDecileTop2,'Bottom')))

                              Above will accumulate but using the load script ordering and I need these rangesum to accumulate from the highest sum(APE) to lowest (descending order). Pls help, thx alot.



                          • Decile Analysis

                            Hi Davis,

                            I am trying to use your slider, but yours is showing the whole category (starting with minimum and ending with the maximum of that category)

                            Mine is only small and at one of the two.

                            Any idea what I am doing wrong?

                            Kind greetings,

                            Wouter

                              • Decile Analysis
                                davissiew

                                Hi Wouter,

                                Its has been a quite some time since this post is updated. Btw I dont get you saying "showing whole category". I recall that there're 3 sliders (Rank, APE Cumm, APE % Cumm). Your category refers to the portion in the slider?

                                  • Decile Analysis

                                    Hi Davis,

                                    Well the problem with the length of the slider was easily fixed, I could just drag him and make him longer. Then 'fix' the length.

                                    Next challenge however, is how to show the data according to the selection in the slider.

                                    In your example you use a macro, is it also possible to use a formula or something?

                                    In the example I have succesfully made the slider, now I would like to adjust the table next to it, according to the vTop and vBottom values...

                                    Thanks for helping me!

                                    greetings,

                                    Wouter Hollander