Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
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%)


ParetoCalc1.png

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

0 Replies