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

18 Replies
Anonymous
Not applicable
Author

Thanks Micheal.

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

This part is getting underlined with a red line.and the chr() function are not activated.

I managed to add parenthesis to it a bit in order to activate them but still get a red underline in here {:
I spaced the expression to delimit the red underline

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

Anonymous
Not applicable
Author

Ignore the red underline, it is not always indicates an error.  Try this in a text box - you see red but it works:

chr(39)&concat(distinct B & chr(39) & ',' & chr(39)) & chr(39)         

Anonymous
Not applicable
Author

I tried ignoring it, but I wouldnt get any form of result

Anonymous
Not applicable
Author

Maybe I mistyped something.  If you can upload a sample of your application, I'll take a look.

Anonymous
Not applicable
Author

Thanks Michael.

Here you go.

Kind regards,

Anonymous
Not applicable
Author

Here it goes:

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

Anonymous
Not applicable
Author

Michael,

it is behaving as sum(C) would.

Whereas what I am trying to achieve is the following:

If I select B1, then the output must exclude all A values that have B1.

Anonymous
Not applicable
Author

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

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

Anonymous
Not applicable
Author

Thank you very much!