Why can't I use IF statement on Expression

[n1ef5ng1]

=if(Activity_Code = 'LOADER',
(Sum({<Service_Type_Indicator = {FOM()'},OPS_Direction = {'EWF'}>}TEU) + (sum({<Service_Type_Indicator={FOM()'},OPS_Direction = {'EWF'}>}Transit_TEU) * 2))
/
(sum({<Service_Type_Indicator = {FOM()'},OPS_Direction = {'EWF'}>}Capacity) * 2)
* 100)

Hi does anybody know what is the problem with this code? My expression of the sum(TEU) is correct but however when i use if statement, it does not show.

For my other report, I can use if statement before fulfilling the sum expression.

I believe is a bug for this qlikview program somehow. Thanks anyone who can solve this problem

[jagan]

  Hi,

Try like this

=((Sum({<Activity_Code = {'LOAD'}, Service_Type_Indicator = {'EFE(MED)'},OPS_Direction = {'E'}>}TEU) + (sum({<Activity_Code = {'LOAD'}, Service_Type_Indicator={'EFE(MED)'},OPS_Direction = {'E'}>}Transit_TEU) * 2))

/

(sum({<Activity_Code = {'LOAD'}, Service_Type_Indicator = {'EFE(MED)'},OPS_Direction = {'E'}>}Capacity) * 2)

* 100

Regards,

Jagan.

[Oleg_Troyansky]

The reason your IF doesn't work is that you have multiple values available for the field Activity_Code, and therefore QV can't resolve the IF question . That's why the need to use AGGR, to ensure that there is only one value available at the level at which the expression is getting aggregated.

Now, since your expression is a ratio, it's most likely not going to calculate the same way with the AGGR (as opposed to a simple sum()). You need to re-formulate your expression in a way that would allow QV to calculate it correctly.

Perhaps, you need to transform the IF condition into another modifier in your Set Analysis - this way, the expression will only calculate the ratio for the required Activity_Code.

As a last resort (and I truly don't recommend it), move your IF condition into your aggregation functions (all of them):

SUM (IF( ...))

This will be much heavier and not scalable at all, but it will be correct from the syntax standpoint, and since the IF will be evaluated for each detailed row, there will always be a single Activity_Code available.