QlikView App Dev

[devarasu07]

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

[sunny_talwar]

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

[tresesco]

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)

[Anil_Babu_Samineni]

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

[devarasu07]

Hi Sunny,

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

[sunny_talwar]

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?

[devarasu07]

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)

[sunny_talwar]

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)

[devarasu07]

Hi tresesco ,

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

Regards,

Deva

[devarasu07]

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.