aggr(rangesum()) and pareto: table with count of products achieving 60% of sales per dimension

[torciaz]

Hello everyone, I'm finding myself in a very tricky situation. 

I have sales data about different product versions of certain products across several markets and months. 

The data is structured like this:

 sample data

I constructed a pareto chart which tells you how many product versions achieve 60% of total sales (and colors them in green). What I want to achieve is the table below the graph: having selected a model, I want to see a row per Market telling me how many Versions of the selected model achieve 60% of total model Sales in that Country (see below). 

The formula i use to get the column named "Versions with 60% sales" is the following: 

count(distinct if(

AGGR(
    rangesum( above(sum(#Sales)/sum(total <Market,Model>#Sales),1,rowno())   )
   ,Model,Market,(Version,(=rank(sum(#Sales),0,1),ASC))

) <=60/100,Version))

This correctly gives me the count of versions whose cumulated sales reach 60% of sales, but only when I select a Market: (see below)

For Market c, 5 versions of Model E accomplish 60% of sales.

However, if I deselect the Market, the same exact count changes and goes to 6. 

I don't understand why the count should change: it probably means that the sorting of the rangesum(above()) is not working properly.

Any information about is greatly appreciated.

Please note that I want a certain Model to be selected to be able to look at the graph. 

Thank you very much 

[lorenzoconforti]

Without the equal sign

count(distinct if(

AGGR(
    rangesum( above(sum(#Sales)/sum(total <Market,Model>#Sales),1,rowno())   )
   ,Model,Market,(Version,(rank(sum(#Sales),0,1),ASC))

) <=60/100,Version))

[torciaz]

Thanks for the reply, but it's not working. Eliminating the equals sign makes qlik consider all the Versions as valid for the count

[lorenzoconforti]

I'll have a better look at it later tonight. For a start I think the color expression is incorrect; I've exported the data and there should be 15 product whose sum of sales is less or equal to 60% while the chart is color coding 16 green:

=aggr(
if(rangesum( above( sum(#Sales)/sum(total #Sales),0,rowno()))<=60/100,'#1b9e77',
if(rangesum( above( sum(#Sales)/sum(total #Sales),0,rowno()))<=80/100,'#ffae34','#ef6f6a'))
,(Version,(=sum(#Sales),Desc)))

[lorenzoconforti]

In the "Versions with 60% sales" measure there were two problems: you were starting to look with a one row offset (same issue you had with the color expression) and you were aggregating by Model (which I don't believe you should be doing).

The amended expression would be:

count(distinct if(

AGGR(
rangesum( above(sum(#Sales)/sum(total <Market>#Sales),0,rowno()) )
,Market,(Version,(=rank(sum(#Sales),0,1),ASC))

) <=60/100,Version))

[torciaz]

thank you for your suggestion but it's not working. 

1) in the rangesum, I set offset to 1 to be sure that at least 60% of sales are covered; otherwise, using your formula, 

it would count the number of versions which account for less or equal than 60%; so I add one to that to be sure to achieve 60%.

To clarify: 

Product	Sales	Cumulative Sales	Cumulative Sales %
A	3	3	30%
B	2	5	50%
C	2	7	70%
D	3	10	100%

In the case above, using my expression would count products A,B and C; 

using your expression would only count products A and B.

2) not aggregating by Model doesn't change the result. The count remains 100% unchanged. Why do you think I shouldn't AGGR by model? 

Feel free to use the qvf I provided to experiment for yourself, if you feel like it. Thanks!