Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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

Anonymous
Not applicable
Author

Hi Anil,

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

Shirley

Anonymous
Not applicable
Author

Hi Deva,

I tried Method 2 and it worked.

thank you so much!

Shirley

ramcena306
Creator II
Creator II

Very Good Explanation Sir!!