topic Re: expression help in QlikView

expression help

bharatkishore — Tue, 22 May 2018 13:38:16 GMT

Hi all,

I have the following expression which i have given in script but the formula is not working at script level where if i take in text box i am geting correct value..

if(Country='Brazil' ,

((if(Strategic_Part='Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.055),0)

Can you please help me why. with the formula what i write in script i am getting 46k but i should get 1.2K

Attached app for reference.

Re: expression help

isingh30 — Tue, 22 May 2018 13:53:00 GMT

May be this -

if(Country='Brazil',if(Strategic_Part='Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01,mat_con_qua))*-1*(([U-D_NL9s]*1.055),0)

Thank you!

Re: expression help

bharatkishore — Tue, 22 May 2018 13:56:57 GMT

Sorry your expression is not working..May be did i miss any brace please let me know.

Re: expression help

bharatkishore — Tue, 22 May 2018 14:33:10 GMT

stalwar1‌ can you please help Sunny bhai..

Re: expression help

sunny_talwar — Tue, 22 May 2018 14:40:42 GMT

[U-D_NL9s] repeats and using Sum([U-D_NL9s]) is causing the problem... if I use this expression in a chart with [U-D_NL9s] as dimension... I see the right number

=(Sum(mat_con_qua*-1)*[U-D_NL9s])*1.055

for text box object, you can try this

=Sum(Aggr((sum(mat_con_qua*-1)*[U-D_NL9s])*1.055, [U-D_NL9s]))

Re: expression help

sunny_talwar — Tue, 22 May 2018 14:41:30 GMT

Or even this

=Sum(mat_con_qua*-1*[U-D_NL9s]*1.055)

Re: expression help

bharatkishore — Tue, 22 May 2018 14:49:43 GMT

Thank you sunny bhai for qucik response, but i need to derive this in script. The condition is

=if(Country='Brazil' ,

(if(Strategic_Part='Tubes' and [U-D_NL9s]<>0, mat_con_qua)*-1)*([U-D_NL9s]*1.055),

(if(Strategic_Part<>'Tubes' and [U-D_NL9s]<>0,mat_con_qua)*-1)*([U-D_NL9s]*1.200))

so in text box i am giving sum to test but else condition is not working or may be i am doing wrong

Re: expression help

sunny_talwar — Tue, 22 May 2018 14:53:14 GMT

Not sure I understand? What is the right number and what aren't you getting? Comparing front end number to back end number is not a smart thing because script go through a lot of changes... what you should do is to figure out the number you are hoping to see based on database or based on talking to users or something to that end.

Re: expression help

bharatkishore — Tue, 22 May 2018 15:03:09 GMT

Sunny Bhai, hope i will explain clearly..

I had a formula where i should take country brazil and Strategic_Part should be tubes and [U-D_NL9s] should not be null and [U-D_NL9s] should not be 0 and [U-D_NL9s] should not be 0.01

if this is satisfied then we need to do mat_con_qua *-1*[U-D_NL9s]*1.055 .

to derive this i have used the following formula

if(Country='Brazil' ,

((if(Strategic_Part='Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.055),0)

I am getting value as 478420 but when i take in a text box it is showing as 12098037.

So is 12098037 is wrong.. please correct me.

Attached app as well.

Re: expression help

sunny_talwar — Tue, 22 May 2018 15:23:10 GMT

okay, so 12098037... what do you want to do? Fix the text box? fix the back end? I already gave you the reason about why text box is showing the wrong number....

Re: expression help

bharatkishore — Tue, 22 May 2018 15:27:13 GMT

Ok thank you sunny bhai.. now i got. one final question sunny bhai i need to pass one else condition i.e.

if(Country='Brazil' ,

((if(Strategic_Part='Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.055),

((if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.200))

but the else condition is not working.. is there anything i am doing wrong?

Re: expression help

sunny_talwar — Tue, 22 May 2018 15:29:04 GMT

Not working in the script?

Re: expression help

bharatkishore — Tue, 22 May 2018 15:31:02 GMT

Not even in text box.. it is showing as o. if i give one condition it is working..

Re: expression help

bharatkishore — Tue, 22 May 2018 15:39:21 GMT

Attached app sunny bhai..

Re: expression help

sunny_talwar — Tue, 22 May 2018 16:05:49 GMT

I think you had a selection on Brazil... remove that it should be fine

Re: expression help

bharatkishore — Tue, 22 May 2018 16:35:15 GMT

Thank u sunny bhai... thank you so much...

Re: expression help

bharatkishore — Tue, 22 May 2018 16:37:12 GMT

Sunny Bhai if country is brazil then is it possible to the value for else condition. sorry to take your time..

Re: expression help

sunny_talwar — Tue, 22 May 2018 21:45:52 GMT

May be this

If(Country='Brazil',

If(Strategic_Part='Tubes',

((if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.055),

((if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua))*-1)*(([U-D_NL9s])*1.200)))

I might have missed a parenthesis... but give it a shot....

Re: expression help

bharatkishore — Wed, 23 May 2018 08:53:23 GMT

Thank you sunny bhai.. thanks a lot..