18 Replies Latest reply: Jun 14, 2013 10:59 AM by Antoine Frangieh

# 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

• ###### Re: Conditional sum / set analysis

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

• ###### Re: Conditional sum / set analysis

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!

• ###### Re: Conditional sum / set analysis

It should be something in this direction:

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

Regards,

Michael

• ###### Re: Conditional sum / set analysis

Thanks Michael.

Indeed that is the way to go and it worked.

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

• ###### Re: Conditional sum / set analysis

Nice :-)

How I forgot about E?  :-(

• ###### Re: Conditional sum / set analysis

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

• ###### Re: Conditional sum / set analysis

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?

• ###### Re: Conditional sum / set analysis

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)

• ###### Re: Conditional sum / set analysis

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)

• ###### Re: Conditional sum / set analysis

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)

• ###### Re: Conditional sum / set analysis

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

• ###### Re: Conditional sum / set analysis

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

• ###### Re: Conditional sum / set analysis

Thanks Michael.

Here you go.

Kind regards,

• ###### Re: Conditional sum / set analysis

Here it goes:

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

• ###### Re: Conditional sum / set analysis

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.

• ###### Re: Conditional sum / set analysis

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

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

• ###### Re: Conditional sum / set analysis

Thank you very much!

• ###### Re: Conditional sum / set analysis

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

Regards.