Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
shirleymoh
Contributor
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
devarasu07
Master II
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:

LOAD * INLINE [

    Product, Store, PurchaseMethod , Sales

    Apple, ABC, 0, 15

    Apricot, ABC, 0, 45

    Avocado, ABC, 0, 10

    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

    Avocado, Online, -1, 70

    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;

Capture.JPG

Hope this helps to you

Regards,

Deva

View solution in original post

5 Replies
Anil_Babu_Samineni

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)
devarasu07
Master II
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:

LOAD * INLINE [

    Product, Store, PurchaseMethod , Sales

    Apple, ABC, 0, 15

    Apricot, ABC, 0, 45

    Avocado, ABC, 0, 10

    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

    Avocado, Online, -1, 70

    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;

Capture.JPG

Hope this helps to you

Regards,

Deva

View solution in original post

shirleymoh
Contributor
Contributor
Author

Hi Anil,

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

Shirley

shirleymoh
Contributor
Contributor
Author

Hi Deva,

I tried Method 2 and it worked.

thank you so much!

Shirley

ramcena306
Creator
Creator

Very Good Explanation Sir!!