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