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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

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

Anil_Babu_Samineni — Sat, 23 Dec 2017 02:12:30 GMT

May be this?

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

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

devarasu07 — Sat, 23 Dec 2017 05:00:56 GMT

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

=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;

Hope this helps to you

Regards,

Deva

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

Anonymous — Sat, 23 Dec 2017 05:20:44 GMT

Hi Anil,

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

Shirley

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

Anonymous — Sat, 23 Dec 2017 05:21:41 GMT

Hi Deva,

I tried Method 2 and it worked.

thank you so much!

Shirley

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

ramcena306 — Sat, 23 Dec 2017 06:42:22 GMT

Very Good Explanation Sir!!

topic Re: How to write expression for Top 10 products by purchase method? in QlikView

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

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

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

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

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

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