Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Your conditions seem to not give any common ArticleNo as an intersection (since you use AND condition). I would rather use the same condition in the expression to respective articles. I have taken ArticleNo as dimension and expression something like:
Sum( {<ArticleNo={"=
rank(-sum({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}>}SoldQty))<=5"}>//*
//<ArticleNo={"=sum(soh)>=400"}>}
//<ArticleNo={"=count(distinct ShopNo)>20"}>
}
SoldQty)
to get :
I just commented out other aggregated conditions because they together give nothing in common as I stated above.
Dimension
=Aggr(If(rank(-sum({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}>}SoldQty))<=5, Only({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}>}ArticleNo)), ArticleNo)
Expression
Sum(SoldQty)
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....
Thanks you very much.Hi stalwar1
Solution working fine with table other chart expect text object. that top 5 images need to show in text box as image as well . i've tried like below way but still it's not working. do you have any suggestion/idea ?
Note: best sellers report able to show as image using text box object. only refer to the below screen shot (table view rank - articleno not matching with text box)
method1:
=only(aggr(if(rank(-sum({<SoldQty={'>0'},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}, ArticleNo = {"=sum(soh)>=400 and count(distinct ShopNo)>20"}>}SoldQty),4)=1, 'http://ip address/Article/'&ArticleNo&'.jpg'), ArticleNo))
method2:
=only(aggr(if(
rank(
-sum({<SoldQty={'>0'},Year={$(vCurrYear)},Week={$(vCurrWeek)},StockType={'Seasonal'},Repeat_Status={'0'}, [# Wks Launched]={'>2'}, ArticleNo = {"=sum(soh)>=400 and count(distinct ShopNo)>20"}>}SoldQty),4)=1,
'http://IP Address/Article/'&ArticleNo&'.jpg'), ArticleNo))
Thanks,
Deva
Hi stalwar1
Thanks for your reply, instead of the article no, need to show that article respective article picture in text object.
trying to show like below (best sellers - article picture instead of that article no) below one is working but this is best sellers expression i the same way tried but worst sellers expression
=only(aggr(if(rank(sum(TotalNetPriceSGD),4)=1, 'http://IP address of the article picture/Article/'&ArticleNo&'.jpg'), ArticleNo))
=only(aggr(if(
rank(
if(
[# Wks Launched]>$(vMaterialAge) and
sum({<Week={$(=only(Week)+1)},StoreName-={'CK Ware House','CKS-WHS','KM','PWP','Virtual Sloc CN'}>}soh)>=$(=vSoh) and
count(distinct {<StoreName-={'CK Ware House','CKS-WHS','KM','PWP','Virtual Sloc CN'}>} StoreName)>$(vStoreCarry),
-sum({<SoldQty={">0"}>}SoldQty))
,4)=1
, 'http://IP Address of the article picture/Article/'&ArticleNo&'.jpg'), ArticleNo))
below table view has right result but need to show the same worst 5 product picture as text object image. i've tried above expression but it's gave wrong rank and article picture.
