Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis

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).

Aggr.PNG

Thanks!

1 Solution

Accepted Solutions
ramoncova06
Specialist III
Specialist III

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

View solution in original post

3 Replies
ramoncova06
Specialist III
Specialist III

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

Not applicable
Author

Thank Ramon!

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

ramoncova06
Specialist III
Specialist III

I am glad it worked