Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MPH
Contributor II
Contributor II

How can I count the number of customers that make up 50 % of Margin

I have used below to calculate the accumulating %

RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $])

 

How would I build off of this to count how many customers make up 50% 

1 Solution

Accepted Solutions
Kushal_Chawda

@MPH  you can try with full descending word

=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), DESCENDING))))

 

View solution in original post

8 Replies
Kushal_Chawda

@MPH   On what dimensions this cumulative % need to be calculated? Can you share sample with expected output?

MPH
Contributor II
Contributor II
Author

Number of unique customers

=count(distinct([Cust Nbr]))  this gives me the total unique customers I want to be able to count how many make up say 80% of margin ( Count of Green Highlighted)

MPH_0-1607984920953.png

 

 

Kushal_Chawda

@MPH  what are the dimension of table?

MPH
Contributor II
Contributor II
Author

Customer ID

Kushal_Chawda

@MPH  try below. sorting is very important here. assuming you have sorted your table with sum([OM $])

=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), Desc))))
MPH
Contributor II
Contributor II
Author

What is the Desc ?

Kushal_Chawda

@MPH  It is sort "Descending" on expression " Sum([OM $])" by customer ID

 

Kushal_Chawda

@MPH  you can try with full descending word

=count(aggr(if(RangeSum(Above(TOTAL Sum([OM $]), 0, RowNo(TOTAL)))/sum(total [OM $]) <=0.8,CustomerID),(CustomerID,(= Sum([OM $]), DESCENDING))))