topic Re: Set Analysis not equal to Syntax in QlikView

Set Analysis not equal to Syntax

cliff_clayman — Thu, 29 Sep 2016 16:37:16 GMT

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

sunny_talwar — Thu, 29 Sep 2016 16:39:30 GMT

May be this:

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

Re: Set Analysis not equal to Syntax

agni_gold — Thu, 29 Sep 2016 16:39:48 GMT

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

cliff_clayman — Thu, 29 Sep 2016 16:50:29 GMT

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

sunny_talwar — Thu, 29 Sep 2016 16:52:09 GMT

Is there a sample you can share?

Re: Set Analysis not equal to Syntax

cliff_clayman — Thu, 29 Sep 2016 16:53:17 GMT

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

nagarjuna_kotha — Thu, 29 Sep 2016 16:54:30 GMT

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

rupamjyotidas — Thu, 29 Sep 2016 16:55:33 GMT

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

stig1984 — Thu, 29 Sep 2016 16:56:05 GMT

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

Re: Set Analysis not equal to Syntax

cliff_clayman — Thu, 29 Sep 2016 16:56:17 GMT

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

Re: Set Analysis not equal to Syntax

cliff_clayman — Thu, 29 Sep 2016 16:59:15 GMT

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

Re: Set Analysis not equal to Syntax

rupamjyotidas — Thu, 29 Sep 2016 17:02:37 GMT

Maybe This.

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

Re: Set Analysis not equal to Syntax

rupamjyotidas — Thu, 29 Sep 2016 17:03:46 GMT

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

cliff_clayman — Thu, 29 Sep 2016 17:10:49 GMT

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

cliff_clayman — Thu, 29 Sep 2016 17:13:36 GMT

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

Re: Set Analysis not equal to Syntax

Anonymous — Fri, 30 Sep 2016 06:53:59 GMT

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

nagarjuna_kotha — Fri, 30 Sep 2016 06:58:27 GMT

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

-Nagarjun

Re: Set Analysis not equal to Syntax

Peter_Cammaert — Fri, 30 Sep 2016 07:16:22 GMT

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

Anonymous — Fri, 30 Sep 2016 07:19:52 GMT

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

Anil_Babu_Samineni — Fri, 30 Sep 2016 07:58:33 GMT

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

Load * Inline [

flag, amount

x, 10

y, 20

z, 30

];

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