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

[Anonymous]

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?

[Anil_Babu_Samineni]

May be this?

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

[devarasu07 · Accepted solution]

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;

Hope this helps to you

Regards,

Deva

[Anonymous]

Hi Anil,

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

Shirley

[Anonymous]

Hi Deva,

I tried Method 2 and it worked.

thank you so much!

Shirley

[ramcena306]

Very Good Explanation Sir!!