topic Re: Expression work for 1 variable but not work for 2 variable in QlikView

Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 01:25:35 GMT

Hi All

I have create a variable = Days

and i insert the above variable to below expression , it work fine :-

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(=vDay)'} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(=vDay)'} >}total_ar * 1))

Due to i need to add one more variable , so i create one more variable = Days1

and again i insert to my expression , but it cannot work.

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(=vDay)''<=$(=vDay1')} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(=vDay)''<=$(=vDay1)'} >}total_ar * 1))

Hope some one can share with me where i go wrong ?

Paul

Re: Expression work for 1 variable but not work for 2 variable

devarasu07 — Mon, 18 Dec 2017 01:56:40 GMT

Hi,

Can you try like below,

Sum({$<SEGMENT -= {'Related Coy'},total_ar={">0"},aging_ar = {'>=$(=$(vDay))<=$(=$(vDay1))'} >}total_ar * 1)

or try this way

Sum({$<SEGMENT-= {'Related Coy'},total_ar={">0"},aging_ar = {'>=$(vDay)<=$(vDay1)'} >}total_ar * 1)

Thanks,

Deva

Re: Expression work for 1 variable but not work for 2 variable

Anil_Babu_Samineni — Mon, 18 Dec 2017 02:12:00 GMT

I already explain, To each From and To buckets you need Double Quotes "" not single one as Set analysis is good for setting. Let's try now

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(=vDay)''<=$(=vDay1')} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(=vDay) <=$(=vDay1)"} >}total_ar * 1))

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {'>=$(vDay)''<=$(vDay1')} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(vDay) <=$(vDay1)"} >}total_ar * 1))

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 06:27:35 GMT

Hi Deva

Thank you very much it work fine.

Paul Yeo

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 06:35:33 GMT

Hi Anil

I have test your expression , it does not work. Since Deva expression is working fine. i am using it. Thank you for your help.

Paul

Re: Expression work for 1 variable but not work for 2 variable

Anil_Babu_Samineni — Mon, 18 Dec 2017 06:39:09 GMT

My Bad, You should've change into If level as well

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(=vDay) <=$(=vDay)"} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(=vDay) <=$(=vDay1)"} >}total_ar * 1))

If(Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(vDay) <=$(vDay1)"} >}total_ar * 1) > 0,

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(vDay) <=$(vDay1)"} >}total_ar * 1))

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 07:05:23 GMT

Hi Anil

Now your expression is working fine. and display the correct amount .

Deva table 1A display 11,766 is correct.

Anil Table 1B display 11,616 is wrong. ( As your expression does not filter Comms Technology Pte LTd amount 150 )

Not sure why ?

Paul

Re: Expression work for 1 variable but not work for 2 variable

Anil_Babu_Samineni — Mon, 18 Dec 2017 07:09:28 GMT

Hi Anil

Now your expression is working fine. and display the correct amount .

Anil Table 1B display 11,616 is wrong

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 07:10:57 GMT

Hi Anil

My typing mistake , Table 1B is correct.

Paul

Re: Expression work for 1 variable but not work for 2 variable

Anil_Babu_Samineni — Mon, 18 Dec 2017 07:12:47 GMT

Quotes in Set Analysis May be helps

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 07:27:23 GMT

Hi Anil

Thank you for your link , yes , i think it is due to the expression reduce to one line from two line. ( I have forgotten why i need 2 line of expression )

Paul

Re: Expression work for 1 variable but not work for 2 variable

Anil_Babu_Samineni — Mon, 18 Dec 2017 07:34:37 GMT

Because, You want to get only more than 0 values. So, The thought is If..Else.. Condition only. To do in single line. You may try this?

Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(vDay) <=$(vDay1)"}, total_ar = {"=Sum({<SEGMENT -= {'Related Coy'},aging_ar = {">=$(vDay) <=$(vDay1)"} >}total_ar * 1)>0"} >}total_ar * 1)

Re: Expression work for 1 variable but not work for 2 variable

paulyeo11 — Mon, 18 Dec 2017 07:38:08 GMT

Hi Anil

Thank you very much for the signal line expression , But the signal line expression seem very long , and more difficult to maintenance.

I will stick with the orgnial expression. Now i am happy that i can reduce the 5 number tab to signal tab.

Paul