Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to build a concentration curve in Qlik Sense.
My source table looks as follows:
Sales Type | Customer | Sales |
---|---|---|
Shop | Frank | 25 |
Web | Frank | 50 |
Web | Lucy | 250 |
Shop | Anna | 10 |
Shop | Tom | 100 |
Web | Tom | 100 |
And I want to obtain something like:
Customer | Sales | Cumulative Sum | Rank |
---|---|---|---|
Lucy | 250 | 250 | 1 |
Tom | 200 | 450 | 2 |
Frank | 75 | 525 | 3 |
Anna | 10 | 535 | 4 |
Ideally, it would also be great if I could represent the concentration curve in a line chart or scatter plot:
Is this possible?
Thanks!
Try this below script:
Temp:
Load * INLINE [
SalesChannel, TypeCustomer, Sales
Shop, Frank, 25
Web, Frank, 50
Web ,Lucy, 250
Shop, Anna, 10
Shop, Tom, 100
Web ,Tom ,100];
AggrData:
Load TypeCustomer, Sum(Sales) As Sales, Sum(Sales) As AccumSales
Resident Temp
Group by TypeCustomer;
Drop Table Temp;
NoConcatenate
SortedData:
Load TypeCustomer, Sales, AccumSales
Resident AggrData
Order by AccumSales desc;
Drop Table AggrData;
NoConcatenate
FinalData:
Load TypeCustomer, Sales, Alt(Peek(AccumSales),0) + Sales As AccumSales, RowNo() As Rank
Resident SortedData;
Drop Table SortedData;
You can try:
I'm using Bar Chart
Dimension: Customer
Measure:
rangesum( above( sum([Sales]),0,rowno()))
Sorting by Expression: sum([Sales])
Hope this helps,
Justin.
sum([Sales]