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

[shree909]

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]

It should be something in this direction:

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

Regards,

Michael

[#4]

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

Regards.

[Anonymous]

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]

Thanks Michael.

Indeed that is the way to go and it worked.

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

[Anonymous]

Nice 🙂

How I forgot about E?  😞

[Anonymous]

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]

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]

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)