Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I have a chart (straight table) with columns region, warehouse, item and quantity. I'm trying to add a new column that shows, for each item, a concatenation of all warehouses that have the item in stock (Qty > 0). I'm getting hung up in the Aggr function or set analysis. The list keeps changing when different selections are made. Can someone have a look? See attached. Thanks.
Maybe something like
=Aggr(
Concat({1} TOTAL<Item> Aggr( If(sum({1} Qty)>0, Only({1}Warehouse)), Region, Item, Warehouse), ',')
,Region,Item,Warehouse)
Do you need this?
Expression:
If(Sum(Qty) > 0, Concat({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse, ','))
Can you give one example what you thing wrong here?
Try something like
If(Sum(Qty) > 0,
Concat(TOTAL<Item> {<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse, ','))
Region | Warehouse | Item | Qty | Warehouses with Stock |
---|---|---|---|---|
72 | ||||
North America | Houston | A1 | 5 | Houston,Montreal,Paris,Warsaw |
North America | Houston | B6 | 2 | Houston,Montreal,Paris |
North America | Houston | E3 | 1 | Houston,Paris,Warsaw |
North America | Houston | G99 | 3 | Houston,Montreal,Warsaw |
North America | Houston | Z8 | 2 | Houston,Montreal,Paris,Warsaw |
North America | Montreal | A1 | 9 | Houston,Montreal,Paris,Warsaw |
North America | Montreal | B6 | 2 | Houston,Montreal,Paris |
North America | Montreal | D9 | 4 | Montreal,Warsaw |
North America | Montreal | G99 | 1 | Houston,Montreal,Warsaw |
North America | Montreal | Z8 | 2 | Houston,Montreal,Paris,Warsaw |
Europe | Paris | A1 | 4 | Houston,Montreal,Paris,Warsaw |
Europe | Paris | B6 | 2 | Houston,Montreal,Paris |
Europe | Paris | Z8 | 1 | Houston,Montreal,Paris,Warsaw |
Europe | Warsaw | A1 | 12 | Houston,Montreal,Paris,Warsaw |
Europe | Warsaw | C2 | 3 | Houston,Montreal,Warsaw |
Europe | Warsaw | D9 | 4 | Montreal,Warsaw |
Europe | Warsaw | E3 | 5 | Houston,Paris,Warsaw |
Europe | Warsaw | Z8 | 10 | Houston,Montreal,Paris,Warsaw |
Item E3 should show "Houston,Warsaw" for all E3 lines regardless of what's selected. Houston, Paris and Warsaw all stock the item E3, but only Houston and Warsaw have stock (Qty > 0). C2 should show only "Warsaw". Etc.
If(Sum(Qty) > 0,
Concat(TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ','))
Region | Warehouse | Item | Qty | Warehouses with Stock |
---|---|---|---|---|
72 | ||||
North America | Houston | A1 | 5 | Houston,Montreal,Paris,Warsaw |
North America | Houston | B6 | 2 | Houston,Montreal,Paris |
North America | Houston | E3 | 1 | Houston,Warsaw |
North America | Houston | G99 | 3 | Houston,Montreal |
North America | Houston | Z8 | 2 | Houston,Montreal,Paris,Warsaw |
North America | Montreal | A1 | 9 | Houston,Montreal,Paris,Warsaw |
North America | Montreal | B6 | 2 | Houston,Montreal,Paris |
North America | Montreal | D9 | 4 | Montreal,Warsaw |
North America | Montreal | G99 | 1 | Houston,Montreal |
North America | Montreal | Z8 | 2 | Houston,Montreal,Paris,Warsaw |
Europe | Paris | A1 | 4 | Houston,Montreal,Paris,Warsaw |
Europe | Paris | B6 | 2 | Houston,Montreal,Paris |
Europe | Paris | Z8 | 1 | Houston,Montreal,Paris,Warsaw |
Europe | Warsaw | A1 | 12 | Houston,Montreal,Paris,Warsaw |
Europe | Warsaw | C2 | 3 | Warsaw |
Europe | Warsaw | D9 | 4 | Montreal,Warsaw |
Europe | Warsaw | E3 | 5 | Houston,Warsaw |
Europe | Warsaw | Z8 | 10 | Houston,Montreal,Paris,Warsaw |
If you want to replace the Aggr() with set analysis, you would need to create a key in your script made of Warehouse and Item, so you can use the advanced search for Qty > 0 on this key.
Thanks, but not quite. It shows different warehouses for E3 for different selections. See the E3 example I posted above.
Try this:
Concat({<Region>}TOTAL <Item> Aggr(If(Sum(Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', ')
If(Sum(Qty) > 0, Concat({<Warehouse,Item >} TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ','))
Or this:
If(Sum(Qty) > 0, Concat({<Region>}TOTAL <Item> Aggr(If(Sum(Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', '))