topic Re: How to count num shops having stock 0 grouped at shop level? in QlikView

How to count num shops having stock 0 grouped at shop level?

Wed, 25 Nov 2020 16:16:04 GMT

Raw Data:			Stock
	Field ITEM_PART_NUMBER		Shop A	Shop B	Shop C	Shop D
Items in substitution group 1 (PIVOT_ITEM_TYPE = 'SUBST'	Item 1	Substituting item	1	1	1	0
	Item 2	Substituded item	1	0	0	0
	Item 3	Substituded item	1	0	0	0
	Item 4	Substituded item	1	0	1	0
	Item 5	Substituded item	1	0	1	0
		Is Stock for all items in Substitution Group 1 aggregated At shop Level > 0 for previous day?	Yes as stock is 5	Yes as stock is 1	Yes as stock is 3	No as stock is 0
Thereby, KPI should return me count of shops = 1 having stock 0 grouped at substitution group 1 level and at each shop for the previous day
My question is how do I calculate a KPI which will return me only the count of shops having stock 0 grouped at substitution group 1 level and at each shop for the previous day?
I have tried the following possibilities but they are not working:
Aggr(sum({$<Date={">=$(=max(Date)-1)<$(=Date)"},Pivot_Item_Type = {'SUBST'}}QTY_STOCK)<=0, SHOP_CODE)
if(Aggr(sum({$<Date={">=$(=max(Date)-1)<$(=Date)"}>}QTY_STOCK), SHOP_CODE, ITEM_PART_NUMBER)=0, Count(DISTINCT SHOP_CODE),1)
if(Aggr(sum({$<Date={">=$(=max(Date)-1)<$(=Date)"}>}QTY_STOCK), ITEM_PART_NUMBER, SHOP_CODE)<=0,0,Count({$} SHOP_CODE))

Re: How to count num shops having stock 0 grouped at shop level?

tunoi — Mon, 08 Aug 2016 09:03:38 GMT

you could try :

count({$<Date={">=$(=max(Date)-1)<$(=Date)"}, Pivot_Item_Type = {'SUBST'}>} Distinct aggr(if( sum({$<Date={">=$(=max(Date)-1)<$(=Date)"},Pivot_Item_Type = {'SUBST'}>}QTY_STOCK) = 0,Shop,null()), Shop,ItemGroup))

Modify the bold text with your dimension.

Re: How to count num shops having stock 0 grouped at shop level?

Mon, 08 Aug 2016 12:03:39 GMT

Thanks Ionut for your suggestion, I'm updating my test data to have the test cases and I will try it very soon and let u know.

Many thanks

Re: How to count num shops having stock 0 grouped at shop level?

Thu, 18 Aug 2016 07:01:14 GMT

Hi ALL

The below expression is working for each line of data I have. THat is great.

(
count({$<Date={">=$(=max(Date)-1)<$(=Date)"}, Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}>} Distinct aggr(if( sum({$<Date={">=$(=max(Date)-1)<$(=Date)"},Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}>}QTY_STOCK) = 0,POC_CODE,null()), POC_CODE, SUBSTITUTION_ITEM))
/
Count({<Z_Active_For_Ava = {1}, Pivot_Item_Type = {'SUBST'}>}DISTINCT POC_CODE)
)

For my next requirement, I need to apply the same expression in a bar chart with Dimension = Date

That is to show the above results for the current selected Date and for the past 45 days.

But as Date is already present in the above expression, how do I modify it to show the expression for the selected Date and for the past 45 days?

That is how do i include the following in my expression: <Date={">=$(=Date(max(Date)-45))<=$(=max(Date))"} >

Thanks for your help