Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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