
Decile Analysis
Rainer Filoda Mar 18, 2010 6:41 AM (in response to davissiew)Hi Davis,
try to use "Fractile". See the attached example for more information.
Good luck!
Rainer

FractileAnalysis_2.qvw 126.8 K

Decile Analysis
davissiew Mar 18, 2010 10:39 PM (in response to Rainer Filoda )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 quartile1 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
Rainer Filoda Mar 19, 2010 3:05 AM (in response to davissiew)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 Mar 31, 2010 5:11 AM (in response to Rainer Filoda )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 inbuilt 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 (rounddown) 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_tmp.qvw 355.5 K

Decile Analysis
davissiew Apr 2, 2010 6:31 AM (in response to 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 May 12, 2010 3:59 AM (in response to 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 May 17, 2010 10:25 PM (in response to 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,'D2',
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
Wouter Hollander Nov 24, 2010 6:56 AM (in response to davissiew)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 Nov 24, 2010 9:53 PM (in response to Wouter Hollander)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
Wouter Hollander Nov 25, 2010 5:01 AM (in response to davissiew)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

80_20_example.qvw 454.0 K




