# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Contributor

## How to write expression for Top 10 products by purchase method?

Hi,

I have 2 different sets of data. 1 with ProductID and Sales and another 1 with PurchaseMethod (0=store purchase, -1=online purchase).

how do I show the top 10 product by sales via online purchase?

1 Solution

Accepted Solutions
Master II

Hi,

By using below method you can achieve it.

Method 1:  Calculated top 10 rank dimension

=aggr(if( rank(sum( {\$<PurchaseMethod={'-1'}>}Sales),4)<=10, Product)) ,Product) // to list top 10 product

or

=aggr(if( rank(sum( {\$<PurchaseMethod={'-1'}>}Sales),4)<=10, rank(sum({\$<PurchaseMethod={'-1'}>}Sales),4)) ,Product) //  to list top 10 product Rank

Note: Remember to suppress null value for this Cal. Dim and then set ascending the sort tab

Method 2: Using Set analysis

sum( {\$<PurchaseMethod={'-1'},Product={"=rank(sum({\$<PurchaseMethod={'-1'}>}Sales),4)<=10"}>} Sales)

set Sales Desc. Order on Sort tab

Method 3: Using Dimension limt (straight table - show largest 10 values)

enable dimension limit option and add dim as product and then add below measure on expression

sum({\$<PurchaseMethod={'-1'}>}Sales)

Method 4: by using back end script

```
Fact:
Product, Store, PurchaseMethod , Sales
Apple, ABC, 0, 15
Apricot, ABC, 0, 45
Banana, ABC, 0, 23
Bilberry, ABC, 0, 31
Blackberry, ABC, 0, 12
Blackcurrant, ABC, 0, 20
Blueberry, NTUC, 0, 12
Boysenberry, NTUC, 0, 70
"Buddha's hand", NTUC, 0, 39
Crab apples, NTUC, 0, 40
Currant, NTUC, 0, 89
Cherry, ShengSiong, 0, 40
Cherimoya, ShengSiong, 0, 89
Chico fruit, ShengSiong, 0, 20
Cloudberry, ShengSiong, 0, 43
Coconut, ShengSiong, 0, 65
Cranberry, ShengSiong, 0, 39
Cucumber, ShengSiong, 0, 23
Custard apple, ShengSiong, 0, 20
Apple, Online, -1, 10
Apricot, Online, -1, 12
Banana, Online, -1, 39
Bilberry, Online, -1, 40
Blackberry, Online, -1, 89
Blackcurrant, Online, -1, 40
Blueberry, Online, -1, 89
Boysenberry, Online, -1, 20
"Buddha's hand", Online, -1, 43
Crab apples, Online, -1, 20
Currant, Online, -1, 10
Cherry, Online, -1, 12
Cherimoya, Online, -1, 70
Chico fruit, Online, -1, 39
Cloudberry, Online, -1, 40
Coconut, Online, -1, 89
Cranberry, Online, -1, 20
Cucumber, Online, -1, 43
Custard apple, Online, -1, 20
];
NoConcatenate
Temp:
Load Product, SUM(Sales) as Sales Resident Fact where PurchaseMethod='-1' Group By Product;

NoConcatenate
Final:
First 10
Load Product, Sales Resident Temp Order By Sales Desc;
Drop Tables Fact, Temp;

```

Hope this helps to you

Regards,

Deva

5 Replies

May be this?

Sum({<PurchaseMethod = {'-1'}, ProductID = {"=Rank(Sum(sales)) <= 10"}>} sales)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Master II

Hi,

By using below method you can achieve it.

Method 1:  Calculated top 10 rank dimension

=aggr(if( rank(sum( {\$<PurchaseMethod={'-1'}>}Sales),4)<=10, Product)) ,Product) // to list top 10 product

or

=aggr(if( rank(sum( {\$<PurchaseMethod={'-1'}>}Sales),4)<=10, rank(sum({\$<PurchaseMethod={'-1'}>}Sales),4)) ,Product) //  to list top 10 product Rank

Note: Remember to suppress null value for this Cal. Dim and then set ascending the sort tab

Method 2: Using Set analysis

sum( {\$<PurchaseMethod={'-1'},Product={"=rank(sum({\$<PurchaseMethod={'-1'}>}Sales),4)<=10"}>} Sales)

set Sales Desc. Order on Sort tab

Method 3: Using Dimension limt (straight table - show largest 10 values)

enable dimension limit option and add dim as product and then add below measure on expression

sum({\$<PurchaseMethod={'-1'}>}Sales)

Method 4: by using back end script

```
Fact:
Product, Store, PurchaseMethod , Sales
Apple, ABC, 0, 15
Apricot, ABC, 0, 45
Banana, ABC, 0, 23
Bilberry, ABC, 0, 31
Blackberry, ABC, 0, 12
Blackcurrant, ABC, 0, 20
Blueberry, NTUC, 0, 12
Boysenberry, NTUC, 0, 70
"Buddha's hand", NTUC, 0, 39
Crab apples, NTUC, 0, 40
Currant, NTUC, 0, 89
Cherry, ShengSiong, 0, 40
Cherimoya, ShengSiong, 0, 89
Chico fruit, ShengSiong, 0, 20
Cloudberry, ShengSiong, 0, 43
Coconut, ShengSiong, 0, 65
Cranberry, ShengSiong, 0, 39
Cucumber, ShengSiong, 0, 23
Custard apple, ShengSiong, 0, 20
Apple, Online, -1, 10
Apricot, Online, -1, 12
Banana, Online, -1, 39
Bilberry, Online, -1, 40
Blackberry, Online, -1, 89
Blackcurrant, Online, -1, 40
Blueberry, Online, -1, 89
Boysenberry, Online, -1, 20
"Buddha's hand", Online, -1, 43
Crab apples, Online, -1, 20
Currant, Online, -1, 10
Cherry, Online, -1, 12
Cherimoya, Online, -1, 70
Chico fruit, Online, -1, 39
Cloudberry, Online, -1, 40
Coconut, Online, -1, 89
Cranberry, Online, -1, 20
Cucumber, Online, -1, 43
Custard apple, Online, -1, 20
];
NoConcatenate
Temp:
Load Product, SUM(Sales) as Sales Resident Fact where PurchaseMethod='-1' Group By Product;

NoConcatenate
Final:
First 10
Load Product, Sales Resident Temp Order By Sales Desc;
Drop Tables Fact, Temp;

```

Hope this helps to you

Regards,

Deva

Contributor
Author

Hi Anil,

for some reason your expression couldn't work, but thank you for helping.

Shirley

Contributor
Author

Hi Deva,

I tried Method 2 and it worked.

thank you so much!

Shirley

Creator

Very Good Explanation Sir!!