Set Analysis

[#1]

How do I alter the following expression to use set analysis rather than an If-statement so that Bill-To Keys <> 1 do not return in the table?

If(Aggr({$<SOURCE = {'ORDER'}>} Count(DISTINCT [Document Counter]), %BILLTO_KEY)=1,Sum([Item Net Value (USD)]))

I have tried this ...

Sum({$<Aggr({$<SOURCE = {'ORDER'}>} Count(DISTINCT [Document Counter]), %BILLTO_KEY)={1}>} [Item Net Value (USD)])

but is does not work (see below).

Thanks!

[ramoncova06 · Accepted solution]

Sum({$<%BILLTO_KEY= {"=Aggr({$<SOURCE = {'ORDER'}>} Count(DISTINCT [Document Counter]), %BILLTO_KEY) <>1"}>} [Item Net Value (USD)])

I haven't tested this yet, though you need to use a search in your set analysis

[#3]

Thank Ramon!

This did the opposite of what I was looking for but I switched the <> to = and it worked.

[ramoncova06]

I am glad it worked