Pareto 80/20 Calculation - Recursive Quick Search Variable Function

[#1]

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