Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
torciaz
Contributor
Contributor

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

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 datasample 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). screen 1.PNG

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)

screen 2.PNG

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. 

screen 1.PNG

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 

5 Replies
lorenzoconforti
Specialist II
Specialist II

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
Contributor
Contributor
Author

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
Specialist II
Specialist II

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
Specialist II
Specialist II

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
Contributor
Contributor
Author

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: 

ProductSales Cumulative SalesCumulative Sales %
A3330%
B2550%
C2770%
D310100%

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!