Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have sample data shown below. I'm looking to build a table that shows the sum of price by item, but also the total price of top 3 items and all other items. I don't think it's possible doing it directly, but wondering if there's any workaround or alternatives?
Output table needed:
Item | Sum (Order Price) |
Watermelon | 11 |
Banana | 9 |
Pear | 6.6 |
Top 3 Total | 26.6 |
All Other | 7.7 |
Grand Total | 34.3 |
Sample Data:
Item | Order Price |
Apple | $ 1.00 |
Apple | $ 1.00 |
Apple | $ 1.00 |
Apple | $ 1.30 |
Apple | $ 1.00 |
Orange | $ 0.80 |
Orange | $ 0.80 |
Orange | $ 0.80 |
Pear | $ 2.00 |
Pear | $ 2.30 |
Pear | $ 2.30 |
Watermelon | $ 3.00 |
Watermelon | $ 2.00 |
Watermelon | $ 5.00 |
Watermelon | $ 1.00 |
Banana | $ 1.00 |
Banana | $ 2.00 |
Banana | $ 3.00 |
Banana | $ 1.00 |
Banana | $ 2.00 |
Add this to the script:
Dim:
LOAD * Inline [
Dim, SortValue
1, 40000
2, 30000
3, 20000
4, 10000
];
and then sort descending by the following expression:
=RangeSum(SortValue + Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]))
May be like this:
Script
Table:
LOAD RowNo() as Key,
Item,
[Order Price]
FROM
[https://community.qlik.com/thread/235197]
(html, codepage is 1252, embedded labels, table is @2);
Dim:
LOAD * Inline [
Dim
1
2
3
4
];
Chart
Dimension
=Pick(Dim, Item, 'Top 3 Total', 'All Other', 'Grand Total')
Expression
=Pick(Dim,
Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]),
Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]),
Sum({<Item = {"=Rank(Sum([Order Price])) >= 4"}>} [Order Price]),
Sum([Order Price]))
Awesome! Seems to work pretty good. What would be the expression for sort order so it's sorted by total price in descending order for top 3 items, then 'Top 3 Total', 'All Other', 'Grand Total'?
Add this to the script:
Dim:
LOAD * Inline [
Dim, SortValue
1, 40000
2, 30000
3, 20000
4, 10000
];
and then sort descending by the following expression:
=RangeSum(SortValue + Sum({<Item = {"=Rank(Sum([Order Price])) < 4"}>} [Order Price]))