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: 
Anonymous
Not applicable

Conditional sum / set analysis

Hello,

I have the following data table:

ABC
Name 1B110
Name 1B220
Name 2B330
Name 2B440

I want to build a chart that can tell me the sum of the names have no B2.

Note that I tried using the following set analysis:

sum({$<B={"*"}-{"B2"}>} C)

However, instead of getting 70 (30+40), I am obtaining 80 (30+40+10) which is logical.

How can I make the expression "behave" so that I can exclude any trace of Name 1 (since one of its occurences contains B2)

Any ideas would be of great value.

Cheers

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Sure...  We select B now, hence affecting A.  See attached.

The most important change here is replacing {$} with {1}

View solution in original post

18 Replies
shree909
Partner - Specialist II
Partner - Specialist II

Hi

IF i understood clearly u want to exclude all the time Field A with value Name1

ucan use this

sum({$<B={"*"}-{"B2"},A-={'Name 1'}>} C)

Thanks

Anonymous
Not applicable
Author

It should be something in this direction:

sum({<A=P({1<B-={'B2'}>})>} C)

Regards,

Michael

Not applicable
Author

Did you try with if( B like '*B2*' ,sum(C))?

Regards.

Anonymous
Not applicable
Author

If I do this, then if changes occur to name 1 by which it doesn't have B2, then I will exclude relevant data.

But thank you for the lead!

Anonymous
Not applicable
Author

Thanks Michael.

Indeed that is the way to go and it worked.

sum( {$<A = E({1<B={‘B2’}>})>} C )

Anonymous
Not applicable
Author

Nice 🙂

How I forgot about E?  😞

Anonymous
Not applicable
Author

Well in your defense, up to until 5 minutes ago, I had no clue that the function even existed.

And I can sleep happier today

Anonymous
Not applicable
Author

It gets trickier.

Let's say that I want to be able to add a parameter instead of the fixed B2 value.

I use then getfieldselections(B) in order to have a variable input

I read that the appropriate syntax would be:

sum( {$<A = E({1<B={"$(=getfieldselections(B)"}>})>} C )

But for some obscure reason, it doesn't work.

a - Is there a known limitation with E() and P() functions by which a function cannot be an argument of the expression?

b - Do I have an error in my syntax?

Anonymous
Not applicable
Author

the problem is that getfieldselections returns comm-separated values, but you need comm-separated values enclosed in single quotation marks.  If you always have something selected in this field, you can use this:

sum( {$<A = E({1<B={chr (39) & concat(distinct B, chr(39) & ',' & chr(39)) & chr(39) }>})>} C)

If there are no selections in field B, the list includes all values, son if it is not desirable, just add a condition that getselectedcount(B)>0.

Regards

Michael

Edit: fixing expression: sum( {$<A = E({1<B={"$(=chr (39) & concat(distinct B, chr(39) & ',' & chr(39)) & chr(39) ) "}>})>} C)