Set Analysis with Concat

[#1]

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.

[swuehl]

If(Sum(Qty) > 0,

Concat({1} TOTAL<Item> Aggr( If(sum({1} Qty)>0, Only({1}Warehouse)), Region, Item, Warehouse), ','))

[#3]

This is closer except now it's hiding the 0 quantity lines...

[sunny_talwar]

You might also need to ignore selection in Region for If(Sum(Qty) > 0 check also...

If(Sum(Qty) > 0, Concat({<Region>}TOTAL <Item> Aggr(If(Sum({<Region>}Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', '))

[sunny_talwar]

You want to see 0 qty lines as well?

Concat({<Region>}TOTAL <Item> Aggr(If(Sum({<Region>}Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', ')

[swuehl]

Well, I am a little confused what you want to see in which column.

I guess, by now you should have a toolbox of possible expressions and set analysis expressions to chose from, if Sunny's last two suggested expressions didn't already make it.

[MK_QSL]

Remove If(Sum(Qty) > 0 from Stefan's reply...

Concat({<Warehouse,Item >} TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ',')

[sunny_talwar]

Hahahahaha

We (especially me) went blazing to provide a solution, without full knowing what was needed exactly

[#9]

I tried that. Now the chart doesn't respond to selections.

The problem with all of these solutions, and the same problem I had originally, is that making different selections (on a warehouse or an item) changes the concatenated list. Item E3, for instance, should show "Houston, Warsaw" on all three lines (even on the Paris line even though it's zero stock) and regardless of what's selected.

[swuehl · Accepted solution]

Maybe something like

=Aggr(

Concat({1} TOTAL<Item> Aggr( If(sum({1} Qty)>0, Only({1}Warehouse)), Region, Item, Warehouse), ',')

,Region,Item,Warehouse)

[sasiparupudi1]

Please try the following

Aggr(NODISTINCT Concat({<Region=,Warehouse={"=aggr(sum({<Region=,Warehouse=>} Qty),Warehouse,Item)>0"}>} Warehouse, ','), Item)