Show Top # total in straight table.

[Anonymous]

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

[sunny_talwar]

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]))

[Anonymous]

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'?

[sunny_talwar · Accepted solution]

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]))