Announcements
cancel
Showing results for
Did you mean:
Master II

## Worst Selling 5 Products?

Hi,

Need to find worst selling 5 product Rank field using below rule,

Tried to create below kind of calculated dimension but it's not working. just shows 0. how to resolve this issue? any suggestions. tks

=aggr(

if(

rank(-(Sum(SoldQty)/Count(DISTINCT ShopNo)),4)<=5

and sum(soh)>=400

and count(distinct ShopNo)>20

and StockType='Seasonal'

and Repeat_Status='0'

and [# Wks Launched]>2

,ArticleNo)

,ArticleNo)

expected output based on above expression

Expected

Thanks,

Deva

1 Solution

Accepted Solutions
MVP

Changing the dimension to this brings me closer to the desired output

=Aggr(If(rank(-sum({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}, ArticleNo = {"=sum(soh)>=400 and count(distinct ShopNo)>20"}>}SoldQty))<=5,

Only({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}, ArticleNo = {"=sum(soh)>=400 and count(distinct ShopNo)>20"}>}ArticleNo)), ArticleNo)

Only difference is the ID ending with 298... Instead of that, I am seeing 324....

36 Replies
MVP

You have more than just one condition here... do you know if all these conditions are ever achieved in the data you have? May be start with just this

=aggr(

if(

rank(-sum({<SoldQty={">0"}>}SoldQty),4)<=5

,ArticleNo)

,ArticleNo)

To see if the bottom 5 shows up or not... because if it does, then the issue might be the intersection of other conditions with this one

MVP

Try putting the scalar conditions in an aggregation function (I take the first one here) like:

=aggr(

if(

rank(-sum({<SoldQty={">0"} , StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]{'>2'}>}SoldQty),4)<=5

and sum(soh)>=400

and count(distinct ShopNo)>20

,ArticleNo)

,ArticleNo)

May be one condition not satisfied. Here, and operator works as all condition should satisfy then only ArticleNo displays.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Master II
Author

Hi Sunny,

Thanks for your kind reply. i've tried and issue still remain. please refer to the attached sample,

MVP

All the lowest ones have 0 quantity... I see this when I uncheck 'Show Zero Value' on the dimension's tab... do you want to ignore 0's?

Master II
Author

Hi Sunny,

Yeah need to exclude 0, actually the rank should calculate measure ( soldqty/count(distinct shopno) )

=aggr(if(rank(-Sum(SoldQty)/Count(DISTINCT ShopNo),4)<=5

and sum(soh)>=400 and count(distinct ShopNo)>20

and StockType='Seasonal'

and Repeat_Status='0'

and [# Wks Launched]>2

,ArticleNo),ArticleNo)

MVP

This gives bottom 5 excluding 0s

=Aggr(If(Rank(If(Sum({<ArticleNo = {"=Sum(SoldQty) > 0"}>}SoldQty) > 0, -Sum({<ArticleNo = {"=Sum(SoldQty) > 0"}>}SoldQty)), 4) <= 5, Only({<ArticleNo = {"=Sum(SoldQty) > 0"}>} ArticleNo)), ArticleNo)

Master II
Author

Hi tresesco ,

Tried your suggestion but still unable to resolve the issue. any other suggestions. many thanks

Regards,

Deva

Master II
Author

Hi Sunny,

how can we add other modifiers in this expression?  Thanks

=aggr(if(rank(-Sum(SoldQty)/Count(DISTINCT ShopNo),4)<=5

and sum(soh)>=400 and count(distinct ShopNo)>20

and StockType='Seasonal'

and Repeat_Status='0'

and [# Wks Launched]>2

,ArticleNo),ArticleNo)

after including all above condition then the expected output will be below 5 Article no only.

Community Browser