Get values belong to N% of total

[muhammadraza]

Hi Guys,

I need to find only those values which belong (less of equal) to the 80% of total sorted from largest to smallest.

There is an option in Qlikview for the same (given below), but want solution in one aggr expression or without using Qlikview (built-in) functionality. Please guide.

Regards,

Muhammad Raza

[Anonymous]

Is there any particular reason behind not using qlikview built -in -functionality . because built in functions make our life easy.

[muhammadraza]

yeah actually I have to use this aggr expression to build more charts on top of it, e.g. I will be showing groups products which belong to these values only and other (not part of 80%) items will not shown.

[maxgro]

PFA, hope it helps

[muhammadraza]

Thanks for the reply, actually I need one (aggr) expression to get the list of values belong to the 80% (i dont need any chart to calculate these values I only need one expression), then I can use that expression to display sum of (80 %) values in a text box, I can also use this aggr expression as calculated dimension to get the relevant values. Hope you get my point.

[muhammadraza]

Aggr(if (RangeSum(Above(total Sum(sales), 0, RowNo(TOTAL))) / Sum(total sales) <= 0.8, Sum(sales)),product), doesn't sort the values but getting the result when giving data in descending order.

[Anonymous]

Hi,

Make sure data is in descending order and try with below syntax:

aggr ( If ( Rangesum ( Above ( sum ( Sales ) ,0 , Rowno()-1 )) /  sum ( Total  Sales ) <= 0.8 , sum(Sales) , product)

Regards

Neetha

[muhammadraza]

what if the data is also containing -ve values and I want to get 100% (all values in my result), in this case it will not work (it will not take all values into the result), see attached. Is there any solution for this.

[Anonymous]

Hi,

what about your expression is it taking negative values inclusive :

Aggr(if (RangeSum(Above(total Sum(sales), 0, RowNo(TOTAL))) / Sum(total sales) <= 0.8, Sum(sales)),product)

Regards

Neetha