Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)) |
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.
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
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