Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
nsnybs21qv
New Contributor III

Show Top # total in straight table.

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:

   

ItemSum (Order Price)
Watermelon11
Banana9
Pear6.6
Top 3 Total26.6
All Other7.7
Grand Total34.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
Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Show Top # total in straight table.

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


Capture.PNG

3 Replies

Re: Show Top # total in straight table.

May be like this:

Capture.PNG

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

nsnybs21qv
New Contributor III

Re: Show Top # total in straight table.

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

Highlighted

Re: Show Top # total in straight table.

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


Capture.PNG