Conditional sum / set analysis

[Anonymous]

Hello,

I have the following data table:

A	B	C
Name 1	B1	10
Name 1	B2	20
Name 2	B3	30
Name 2	B4	40

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

[Anonymous]

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]

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]

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

[Anonymous]

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

[Anonymous]

Thanks Michael.

Here you go.

Kind regards,

[Anonymous]

Here it goes:

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

[Anonymous]

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 · Accepted solution]

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

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

[Anonymous]

Thank you very much!