Set analysis and IF statement

[#1]

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.

[Anonymous]

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] )

[#3]

Yes, i supposed this would work. Thanks Bill

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

[#4]

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.

[#5]

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

[#6]

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.