topic Expression help in QlikView

Expression help

bharatkishore — Tue, 15 May 2018 13:00:28 GMT

Hi All,

i Have the below expression written in script:

(if(if(IsNull(YTD_Consumption_LY),0,YTD_Consumption_LY)=0,if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY),

if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY))*(ADJ_CONS_YTD_LY/YTD_Consumption_LY)) as Adj_Consumption,

I am getting the value as 467K

Now when i write the below expression in text box i.e.

=sum(if(if(IsNull(YTD_Consumption_LY),0,YTD_Consumption_LY)=0,if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY),

if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY)))*(sum(ADJ_CONS_YTD_LY)/sum(YTD_Consumption_LY))

i am gettiing the value i.e. 568K

Can you please help me where i am doing wrong in my script expression..

Thanks,

Bharat

Re: Expression help

YoussefBelloum — Tue, 15 May 2018 13:18:36 GMT

Hi,

would you be able to attach sample data or sample app ?

Re: Expression help

olivierrobin — Tue, 15 May 2018 13:25:47 GMT

hello

maybe the 2 sum() functions in your 2nd expression

if i understand, the 1st one i equivalent to a*b . if you sum, it is sum(a*b)

and in the 2nd, it is more sum(a)*sum(b)

which is mathematically different

Re: Expression help

bharatkishore — Tue, 15 May 2018 13:42:54 GMT

Hi Robin,

The correct formula is:

=sum(Cons_YTD_LY)*(sum(ADJ_CONS_YTD_LY)/sum(YTD_Consumption_LY))..

I need to represent this in

(if(if(IsNull(YTD_Consumption_LY),0,YTD_Consumption_LY)=0,if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY),

if(IsNull(Cons_YTD_LY),0,-Cons_YTD_LY))*(ADJ_CONS_YTD_LY/YTD_Consumption_LY))

Can you please tell where i am doing wrong

Re: Expression help

olivierrobin — Tue, 15 May 2018 13:48:03 GMT

the 1st one can be resumed as sum(a)*sum(b)/sum(c)

so you have to express your 2nd one on the same format, maybe like

sum(if( ........,a))*sum(if( .......,b))/sum(if( .......,c))

where if(....) is your if formulas and a,b,c, the 3 expressions you want to sum

Re: Expression help

bharatkishore — Tue, 15 May 2018 14:31:11 GMT

Hi,

I am just writing a simple expression as below:

Cons_YTD_LY*(ADJ_CONS_YTD_LY/YTD_Consumption_LY) but still the value is not matching..

Can you please let me know where iam doing wrong

Re: Expression help

olivierrobin — Tue, 15 May 2018 14:38:16 GMT

you have to write your if() expressions

if(......,Cons_YTD_LY)*if(........,ADJ_CONS_YTD_LY)/if(......,YTD_Consumption_LY)

unless there are the same for each member, and is this case

if(........,Cons_YTD_LY*(ADJ_CONS_YTD_LY/YTD_Consumption_LY))