topic Re: Set analysis and IF statement in QlikView

Set analysis and IF statement

Tue, 02 Sep 2014 16:19:02 GMT

I have an expression;

=sum(IF(DIRECTION='B',-[TRADE SIZE],[TRADE SIZE])*PRICE/100)

When I select the value 'YES' in the field 'IS FIXING TRADE' it gives me a particular number. However, I need to adapt this expression elsewhere in my doc and stipulate 'IS FIXING TRADE' is always YES.

This is how i adpated it

=sum({$<[IS FIXING TRADE]={'YES'}>}(IF(DIRECTION='B',-[TRADE SIZE],[TRADE SIZE])*PRICE/100))

The problem is that I get two different results, when they should be the same. What am I doing wrong in my second expression that means the result is different to the first where I manually select the 'YES' value.

Re: Set analysis and IF statement

Anonymous — Tue, 02 Sep 2014 16:23:53 GMT

How about moving the IF to your load script with something like :

IF(DIRECTION='B',-[TRADE SIZE],[TRADE SIZE])*PRICE/100 as [CalcSize] ,

Then the Set Analysis should simplify to :

=sum({$<[IS FIXING TRADE]={'YES'}>} [CalcSize] )

Re: Set analysis and IF statement

Tue, 02 Sep 2014 16:30:55 GMT

Yes, i supposed this would work. Thanks Bill

However I am still curious as to what i was doing wrong in my expression!

Re: Set analysis and IF statement

Tue, 02 Sep 2014 16:31:00 GMT

It seems like the expression should work, at first glance. What is the context around these two expressions, are they in charts, text objects, etc?

If they are in charts, try putting them side-by-side in text objects to see if there is still a discrepancy.

Re: Set analysis and IF statement

Tue, 02 Sep 2014 16:34:17 GMT

They're in charts, with the values on each data point, so I can see the differences clearly.

The dimension is calendar week in both cases on a bar chart

Re: Set analysis and IF statement

Tue, 02 Sep 2014 16:49:50 GMT

Apologies if I was unclear. I use text objects as a troubleshooting step for expressions not because it is easier to see the difference, but because it rules out any properties specific to the chart/dimensions as the problem.

I suspect there is something related to the data model going on here, as your expression is working fine for me on a small amount of demo data.