Skip to main content
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pareto 80/20 Calculation - Recursive Quick Search Variable Function

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...





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


            ))<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...


=$(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:


    // Exit recursion when found

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


    // 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:


The definition for v_ParetoFinished is:

    $1 + 1 // exits the recursive loop


Total Length (minutes): 444,948

80% Length (minutes): 355,958

Number of Years: 95

Years making up 80% of Minutes: 59

Example QVW attached



0 Replies