topic Re: Show Top # total in straight table. in QlikView

Show Top # total in straight table.

Anonymous — Tue, 04 Oct 2016 16:19:57 GMT

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

Re: Show Top # total in straight table.

sunny_talwar — Tue, 04 Oct 2016 16:27:19 GMT

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

];

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([Order Price]))

Re: Show Top # total in straight table.

Anonymous — Tue, 04 Oct 2016 16:57:30 GMT

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

Re: Show Top # total in straight table.

sunny_talwar — Tue, 04 Oct 2016 17:02:21 GMT

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