Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results 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...

=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
Community Browser