topic Worst Selling 5 Products? in QlikView

Worst Selling 5 Products?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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)

expected output based on above expression

Expected

Thanks,

Deva

Re: Bottom 5 Product?

sunny_talwar — Thu, 04 May 2017 11:12:36 GMT

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)

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

Re: Bottom 5 Product?

tresB — Thu, 04 May 2017 11:12:38 GMT

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)

Re: Bottom 5 Product?

Anil_Babu_Samineni — Thu, 04 May 2017 11:13:33 GMT

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

Re: Bottom 5 Product?

devarasu07 — Thu, 04 May 2017 11:20:33 GMT

Hi Sunny,

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

Re: Bottom 5 Product?

sunny_talwar — Thu, 04 May 2017 11:24:11 GMT

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?

Re: Bottom 5 Product?

devarasu07 — Thu, 04 May 2017 11:33:05 GMT

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)

Re: Bottom 5 Product?

sunny_talwar — Thu, 04 May 2017 11:39:41 GMT

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)

Re: Bottom 5 Product?

devarasu07 — Thu, 04 May 2017 11:42:44 GMT

Hi tresesco ,

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

Regards,

Deva

Re: Bottom 5 Product?

devarasu07 — Thu, 04 May 2017 11:50:04 GMT

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.

Re: Bottom 5 Product?

tresB — Thu, 04 May 2017 11:53:39 GMT

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.

Re: Bottom 5 Product?

devarasu07 — Thu, 04 May 2017 12:04:37 GMT

Hi tresesco‌,

is it possible to have Rank field (as calculated dimension) because my report has too many measures and also i need to show bottom 5 article picture through text box object.

Many Thanks

Regards,Deva

Re: Bottom 5 Product?

sunny_talwar — Thu, 04 May 2017 13:04:27 GMT

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)

Re: Worst Selling 5 Products?

sunny_talwar — Thu, 04 May 2017 13:16:47 GMT

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....

Re: Worst Selling 5 Products?

devarasu07 — Thu, 04 May 2017 14:36:46 GMT

Hi Sunny,

Your always Perfect Thanks you very much.

Regards,
Deva

Re: Worst Selling 5 Products?

devarasu07 — Mon, 08 May 2017 12:52:26 GMT

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

Re: Worst Selling 5 Products?

sunny_talwar — Mon, 08 May 2017 13:03:01 GMT

Try this

=FirstSortedValue(ArticleNo, -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,

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

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

...

Re: Worst Selling 5 Products?

devarasu07 — Mon, 08 May 2017 18:45:33 GMT

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.

Re: Worst Selling 5 Products?

sunny_talwar — Mon, 08 May 2017 18:53:36 GMT

Not sure why your's isn't working because I don't see the images when I open the dashboard... but see if this works or not

=FirstSortedValue('http://192.168.1.3/Article/'&ArticleNo&'.jpg', -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,

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

Re: Worst Selling 5 Products?

devarasu07 — Mon, 08 May 2017 18:58:50 GMT

Hi Sunny,

This expression works fine. many thanks for your prompt response

Regards,

Deva