Pareto 80/20 Calculation - Recursive Quick Search Variable Function

Mon, 31 Jul 2017 00:34:57 GMT

How many years account for 80% of the minutes (length) of all films produced? 59 of 95 (62%)

If you are using QV12.10 or QS versions after Dec 2016 then the preferred Pareto Calculation uses the updated AGGR sorted by an expression...

=Count(Distinct

Aggr(

If(

Rangesum(Above(

Sum({1} Minutes)/Sum({1} total Minutes)

,1,RowNo()

))<0.8, Year),

(Year,(=Sum({1} Minutes),Desc))

)

Recipe for a Pareto Analysis – Revisited(thanks @HIC)

---------------------------------------------------------------------------------------------------------------

... however there is an alternative method using a recursive variable function performing a quick search that will work in older versions. It is not simple, but it is effective and is a possible the recipe for other bespoke calculations requiring a recursive variable function...

Usage:

=$(v_ParetoRecursive(1, 4096))

$1 lower bound of quick search

$2 upper bound of quick search

The calculation will iterate 12 times (2^12 = 4096) for the above example

Variables required:

The definition for v_ParetoRecursive is:

$(=$(v_Pareto$(=

// Exit recursion when found

if($1=$2, 'Finished', 'Recursive')

)($(=if(

// Expression Sum(Minutes) ranked by dimension Year

Sum({$< [Year]={"=rank(sum(Minutes))<=$(=Round(($1+$2)/2))"} >} Length)

> v_Pareto80Pct,

'$1, ' & (Round(($1+$2)/2)-1), // Search lower half

(Round(($1+$2)/2)) & ', $2' // Search upper half

)))))

Sum(Minutes) is the expression being ranked

Year is the dimension being ranked

The definition for v_Pareto80Pct is:

=Sum(Minutes)*0.8

The definition for v_ParetoFinished is:

$1 + 1 // exits the recursive loop

Results:

Total Length (minutes): 444,948

80% Length (minutes): 355,958

Number of Years: 95

Years making up 80% of Minutes: 59

Example QVW attached

Thanks

Brett

topic Pareto 80/20 Calculation - Recursive Quick Search Variable Function in QlikView

Pareto 80/20 Calculation - Recursive Quick Search Variable Function