topic Re: set expression in QlikView

set expression

upaliwije — Tue, 04 Jul 2017 15:20:47 GMT

Hi Friends,

I want to calculate renewed premium in respect of insurance business and I have developed a QV doc which I have attached h/w

In the above renewed premium column the highlighted amount should not appear. Pls help me to modify the expression in my attached QV doc

Re: set expression

vishsaggi — Tue, 04 Jul 2017 16:14:18 GMT

Why those values should not be there?

Re: set expression

tajmohamed30 — Tue, 04 Jul 2017 16:15:47 GMT

how about removing your expression by keeping only the label, and modify the null and missing symbol in the presentation tab

Re: set expression

upaliwije — Tue, 04 Jul 2017 16:18:36 GMT

Thanks

Rs.2538 should not be there since the policy is not renewed for the next year

Rs.3088 the policy is cancelled and cant be renewd

Re: set expression

upaliwije — Tue, 04 Jul 2017 16:20:32 GMT

I do not understand what you say. Pls clarify

Re: set expression

tajmohamed30 — Tue, 04 Jul 2017 16:39:16 GMT

my suggestion hiding by numbers is not correct as it may vary according to data, how about excluding the above 2 values by columns using if condition or set analysis

Re: set expression

upaliwije — Tue, 04 Jul 2017 16:44:25 GMT

That is exactly what I want. I am unable to modify the expression for that. can help me pls

Re: set expression

tajmohamed30 — Tue, 04 Jul 2017 16:54:25 GMT

you can try e() particular column or -= for particular value in your case if it has policy is not renewed for the next year and the policy is cancelled and cant be renewed at Set Analysis Wizard for QlikView | qlikblog.at

Re: set expression

vishsaggi — Tue, 04 Jul 2017 16:56:05 GMT

How do you identify if that policy is not renewed or cancelled? Is there any flag or what is the pointer here?

Re: set expression

upaliwije — Wed, 05 Jul 2017 03:18:37 GMT

Thanks Taj

Could you pls write the modified expression in my sample file and uplaod.

Re: set expression

upaliwije — Wed, 05 Jul 2017 03:38:47 GMT

Hi,

Refunds are recorded in column 5 while max Period_to field is the current period for non renewed policies

Re: set expression

upaliwije — Wed, 05 Jul 2017 05:16:43 GMT

Hi Friends

Thank you all for helping me. I have found a solution to my problem. But I have a small issue where I do not get the correct total in the last column of the below pivot table pls look into this and help me

My expression for the last column is as follows

=if(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )>0,

(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )

-sum( {$<[R_YEAR] = {'$(=max([R_YEAR]))'}>} [PREMIUM] )