27 Replies Latest reply: Mar 20, 2017 4:24 AM by Peter Cammaert

# Set Analysis not equal to Syntax

I am trying to use Set Analysis for a not equal to value.

=Sum({\$<[Flag]-={'x'}>}[Amount])

This is not working properly.  When I change -= to just = , I get the correct amount for the ones that should be equal to x.  How should I write this to sum the Amount where the Flag is not equal to x?

• ###### Re: Set Analysis not equal to Syntax

May be this:

=Sum({\$<[Flag] = e({<Flag = {'x'}>})>}[Amount])

• ###### Re: Set Analysis not equal to Syntax

Am I right ?

=Sum( {1- \$<[Flag]={'x'}> } [Amount] )

• ###### Re: Set Analysis not equal to Syntax

Try with out \$ ,

As it is looking fine , can you post your sample app so that i can help easily.

• ###### Re: Set Analysis not equal to Syntax

Sunny,

That expression just returns \$0.

Agnivesh,

Taking out the \$ also gives me \$0.

I am essentially trying to replace this expression with Set Analysis:

=Sum(If([Flag]<>'x',[Amount]))

• ###### Re: Set Analysis not equal to Syntax

Is there a sample you can share?

• ###### Re: Set Analysis not equal to Syntax

Not really as you have seen before, the file is quite large and I cannot just parse out the relevant data.

• ###### Re: Set Analysis not equal to Syntax

You can also try like below:

Exmple:   Sum( {<Year -= {2016}>} Sales)

I hope thi may helps you.

-Nagarjun

• ###### Re: Set Analysis not equal to Syntax

I have tried that as well and it produces a value of \$0.

• ###### Re: Set Analysis not equal to Syntax

What does If([Flag]<>'x' returns, it is NULL or you have a value for it like Flag<>'x' is 'Y','Z' etc, as NULL would be a problem in Set Analysis

• ###### Re: Set Analysis not equal to Syntax

I think that is the issue.  How do I account for NULL values then?

• ###### Re: Set Analysis not equal to Syntax

Maybe This.

=sum({(\$ -<Flag={'*'}>) Sales)

• ###### Re: Set Analysis not equal to Syntax

Or define something for NULL in Script

If(Flag<>'X', Y) as Flag

and use Set Analysis in equation

• ###### Re: Set Analysis not equal to Syntax

That doesn't work.  If I cannot determine a way to do it within Set Analysis, I will just add something to the script that gives it a value when it is not 'x'.

• ###### Re: Set Analysis not equal to Syntax

Try =Sum({\$<[Flag] = - {'x'}>}[Amount])

• ###### Re: Set Analysis not equal to Syntax

That does not work, most likely because of the NULL values as well.

• ###### Re: Set Analysis not equal to Syntax

Hi Cliff, I prefer to do the exclusion in two steps to manage null values better:

=Sum(Amount)-Sum({\$<[Flag]={'x'}>}[Amount])

Regards!!

• ###### Re: Set Analysis not equal to Syntax

If want to get solution quickly, try to share sample data or else Qvw.

-Nagarjun

• ###### Re: Set Analysis not equal to Syntax

If the rows where Flag is a field have some sort of unique ID, you could try with an expression search. For example:

=Sum({<SalesOrderID = {"=Flag <> 'x'"}>} Amount)

• ###### Re: Set Analysis not equal to Syntax

Hi Cliff,

Can you try below expression. I just removed the quotes in the value.

=Sum({\$<[Flag]-={x}>}[Amount])

Hope this time it should work.

• ###### Re: Set Analysis not equal to Syntax

Perhaps this

=Sum({\$<flag -= {'x'}>} amount)

I've written all lower letters

The better Sample Data. Why this is not working? Have you get any typo error from Expression

flag, amount

x, 10

y, 20

z, 30

];

For me this expression is working. Would you provide data which you expecting

• ###### Re: Set Analysis not equal to Syntax

May be

Sum(If(Len(Trim(Flag))=0,Amount)

• ###### Re: Set Analysis not equal to Syntax

=Sum({\$<[Flag]= e({1<id={'X'}>})>}[Amount])

try this

• ###### Re: Set Analysis not equal to Syntax

corrected one

=Sum({\$<[Flag]= e({1<[Flag]={'X'}>})>}[Amount])

• ###### Re: Set Analysis not equal to Syntax

Hi Cliff,

May be try,

=Sum({\$<Flag ={'*'}-{'x'}>}[Amount])

• ###### Re: Set Analysis not equal to Syntax

Hi , you need to change from  " -= "    to   " =- " and it will work.

=Sum({\$<[Flag]=-{'x'}>}[Amount])

• ###### Re: Set Analysis not equal to Syntax

Indeed, it all depends on what should be the initial selection:

• Flag -= {'x'}  Current selection minus those rows with Flag = x
• Flag =- {'x'}  All rows with flag values (including those not selected), excluding those with Flag = x.

However, from the OP we cannot deduce in detail what is the initial situation...