Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
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 output.png

Expected

Thanks,

Deva

1 Solution

Accepted Solutions
sunny_talwar

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)


Capture.PNG

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

View solution in original post

36 Replies
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
MVP
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)

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
devarasu07
Master II
Master II
Author

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?

Capture.PNG

devarasu07
Master II
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)

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)


Capture.PNG

devarasu07
Master II
Master II
Author

Hi tresesco ,


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


Regards,

Deva

devarasu07
Master II
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.

Expected output.png