topic Why can't I use IF statement on Expression in QlikView

Why can't I use IF statement on Expression

n1ef5ng1 — Wed, 28 Aug 2013 10:13:49 GMT

=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

Re: Why can't I use IF statement on Expression

MayilVahanan — Wed, 28 Aug 2013 10:16:41 GMT

I think, you missed open quotes before FOM()?

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

And check

(sum({<Service_Type_Indicator = {'FOM()'},OPS_Direction = {'EWF'}>}Capacity) * 2) not an zero values?

Re: Why can't I use IF statement on Expression

n1ef5ng1 — Wed, 28 Aug 2013 10:19:06 GMT

this is my code. it does contain values. afterwhich i added a if statement as i need to eliminate certain records

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

Re: Why can't I use IF statement on Expression

MayilVahanan — Wed, 28 Aug 2013 10:27:13 GMT

Can you provide a sample file?

Activity_Code contains "LOAD" or "Load", is there relation between Activity_Code and Dimension?

It helps for us.

Re: Why can't I use IF statement on Expression

CELAMBARASAN — Wed, 28 Aug 2013 10:27:20 GMT

What are the dimesions you have used?

Based on your expression Activity_Code should be specified in dimension.

If possible can you post sample app.

Re: Why can't I use IF statement on Expression

abeyphilip — Wed, 28 Aug 2013 10:37:22 GMT

Hi Benn,

Not sure if this is the problem, added one extra bracket before multiplying with 100.

if(Activity_Code = 'LOAD',

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

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

* 100)

Rgds,

Abey

Re: Why can't I use IF statement on Expression

matthieu_burel — Wed, 28 Aug 2013 10:48:15 GMT

You formula can working depending how you use it.

Is it in the script ? a text-object ? a graph ?

Re: Why can't I use IF statement on Expression

n1ef5ng1 — Wed, 28 Aug 2013 10:58:54 GMT

My dimensions is just month year. I diden use activity code on my dimension.

Sent from my HTC

Re: Why can't I use IF statement on Expression

CELAMBARASAN — Wed, 28 Aug 2013 11:17:20 GMT

Then you should use aggr around that

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

Re: Why can't I use IF statement on Expression

n1ef5ng1 — Thu, 29 Aug 2013 00:47:19 GMT

this works but does this works the same as my previous code, as in the logic behind it.

Not well versed on aggr at the moment

Re: Why can't I use IF statement on Expression

jagan — Thu, 29 Aug 2013 03:41:28 GMT

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.

Re: Why can't I use IF statement on Expression

Oleg_Troyansky — Thu, 29 Aug 2013 04:11:58 GMT

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.