Not available in current selection, but are in total selections

[JordyWegman]

Hi,

I have the following situation. We have an article that is available in a DC, say in BB. This article can have two statusses, A or B, say available or not available. This DC delivers to a certain country and multiple DCs can deliver to the same country. Besides that, articles can be listed to a certain country. For example, some products are sold in DE but not in NL.

What we want to make is a result that tells us: If you have an article that is not available in DC BC. We want to know in which DCs this article is available. The result should be a concatenated string of those DCs.

Article	Art.Status	DC	Country	Location - Country	Listed	Result
1	A	BB	DE	BB-DE	Y
1	A	BC	NL	BC-NL	N
1	B	BC	FR	BC-FR	Y	BB-BD-BA
1	B	BC	BE	BC-BE	Y	BB-BD-BA
1	A	BD	BE	BD-BE	Y
1	A	BA	FR	BA-FR	Y

This is what we have tried and many derivatives:

IF( Art.Status = B,

   Aggr({$< Art.Status = {'B'}>}

   concat( TOTAL {$< Art.Status = {'A'},[DC]= >}[DC],'-')

   ,Location-Country, Article, Listed)

,null()

)

Can someone push this in the right direction?

Kind Regards,

- Jordy

[Or]

Your code looks like it's pretty much correct (once I corrected the syntax and field names).

Why does the expected result not include BC NL (line 2) in the result? It has Article 1 listed, and it has status = A. This means it covers all of the requirements in your formula. Should there be another set modifier to exclude Listed = 'N'?

[JordyWegman · Accepted solution]

It wasn't possible, or really complex maybe, so instead of doing this is the front-end I made it in the datamodel. Thanks for the help!