topic Set Analysis in QlikView

Set Analysis

Wed, 17 Jun 2015 20:24:35 GMT

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 — Wed, 17 Jun 2015 20:34:26 GMT

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

Wed, 17 Jun 2015 20:38:02 GMT

Thank Ramon!

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

ramoncova06 — Wed, 17 Jun 2015 20:44:25 GMT

I am glad it worked