Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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