topic Re: Expression in QlikView

Expression

krishna20 — Tue, 01 Jul 2014 06:41:07 GMT

Hi Friends

I have given nested if using set analysis to retrieve the outstanding amount by comparing years.I dont know where i have done the mistake,i'm getting null result.Please help me out friends.

=if(CE_CLOSE_YN='1' and CE_CLOSE_DT<Today(),0,if(CE_CLOSE_YN>='1' and CE_CLOSE_DT>Today(),(sum({$<Year={"vCurrentYear-2>vCurrentYear-1<vCurrentYear"}>}CE_AMT_LC_1))))

Regards

Krishna

Re: Expression

Tue, 01 Jul 2014 06:42:07 GMT

expand vCurrentYear

$(vCurrentYear)

Re: Expression

Tue, 01 Jul 2014 06:43:43 GMT

also if it is the last 3 years that you want to see you can simply say

$<Year={">=$(vCurrentYear)-3"}

Re: Expression

tresB — Tue, 01 Jul 2014 06:43:59 GMT

It depends majorly on your date field format; variable outcome and the place(chart or text box) where you are using this expression. Try to upload your sample qvw.

Re: Re: Expression

krishna20 — Tue, 01 Jul 2014 06:55:11 GMT

Hi Tresesco

Please find the attachment and in Loss ratio for the filed Claim OS and Earned Premium.I want the data for 2014,2013,2012.Please help me out

Regards

Krishna

Re: Expression

krishna20 — Tue, 01 Jul 2014 07:01:14 GMT

Hi Tresesco

I'm getting the same result for the Gross Premium and Earned Premium even i applied if else condition.The if else not working properly for the earned premium field.Please check it and guide me further.

Regards

Krishna

Re: Expression

krishna20 — Tue, 01 Jul 2014 07:04:09 GMT

Hi Ram

Thank you for your Reply.Getting same result after applying the expression $<Year={">=$(vCurrentYear)-3"}

Regards

krishna

Re: Expression

tresB — Tue, 01 Jul 2014 07:05:48 GMT

I am lost in your app., Which Sheet/tab->chart?

Re: Expression

krishna20 — Tue, 01 Jul 2014 07:07:56 GMT

Sheet name is Loss ratio.straight table

Re: Expression

rohit214 — Tue, 01 Jul 2014 07:10:49 GMT

Hi,

Try that

=if(CE_CLOSE_YN='1' and CE_CLOSE_DT<Today(),0,(sum({$<Year={"vCurrentYear-2>vCurrentYear-1<vCurrentYear"}>}if(CE_CLOSE_YN>='1' and CE_CLOSE_DT>Today()CE_AMT_LC_1))))

Thanks

Rohit

Re: Expression

tresB — Tue, 01 Jul 2014 07:13:06 GMT

Could you just upload the qvw file? Having issue in extraction.

Re: Expression

SunilChauhan — Tue, 01 Jul 2014 07:16:01 GMT

dont use 1 in sigle quotes as it is number we can have only text in ' '

use below code

if(CE_CLOSE_YN=1 and CE_CLOSE_DT<Today(),0,if(CE_CLOSE_YN>=1 andCE_CLOSE_DT>Today(),(sum({$<Year={"$(vCurrentYear)-2>$(vCurrentYear)-1"}>}CE_AMT_LC_1))))

what year range do you want to have in your code?

hope this helps

Re: Expression

krishna20 — Tue, 01 Jul 2014 07:21:32 GMT

Hi Sunil

1 is a flag in my QVD.years are 2014,2013,2012

Regards

krishna

Re: Expression

SunilChauhan — Tue, 01 Jul 2014 07:25:08 GMT

try below code

if(CE_CLOSE_YN='1' and CE_CLOSE_DT<Today(),0,if(CE_CLOSE_YN>='1'and CE_CLOSE_DT>

Today(),(sum({$<Year={">=$(=Max(Year)-2)<=$(=Max(Year))"}>}CE_AMT_LC_1))))

hope this helps

Re: Expression

krishna20 — Tue, 01 Jul 2014 07:45:15 GMT

Hi Sunil,

Its not working