topic Re: Calculation in QlikView

Calculation

upaliwije — Fri, 01 Sep 2017 10:25:22 GMT

I have following pivot table in QV doc

I have calculated the growth from the following expression

(column(2)-column(1))/fabs(column(1))*100

I want to modify this expression to calculate the growth only for NEW and RENEWALS (rows top 2). Accordingly I have written below expression

if(ValueList('NEW','RENEWALS'),(column(2)-column(1))/fabs(column(1)),0)*100

But It does not work. Have I made any mistake pls correct me

Re: Calculation

techvarun — Fri, 01 Sep 2017 10:31:23 GMT

try like below

If (Valuelist('New','Renewal') = 'New',(column(2)-column(1))/fabs(column(1)),0)*100)

Re: Calculation

techvarun — Fri, 01 Sep 2017 10:34:41 GMT

The entire description column is a value list then you need to include all the values in the value list

For eg

If(ValueList(('New','Renewal',....... 'Net Total) = 'New',(column(2)-column(1))/fabs(column(1)),0)*100),

If(ValueList(('New','Renewal',....... 'Net Total) = 'Renewal',"Formula", etc

If you feel the expression is long, alternatively you can use a variable or Pick and Match function.

Re: Calculation

effinty2112 — Fri, 01 Sep 2017 10:35:14 GMT

Hi Upali,

What about:

if(match(Description,'NEW','RENEWALS'),(column(2)-column(1))/fabs(column(1))*100)

Cheers

Andrew

Re: Calculation

Anil_Babu_Samineni — Fri, 01 Sep 2017 10:35:51 GMT

Better, If you provide sample application?

Perhaps this?

You can use Column(1) and Column(2) expressions to degrade like Description = {'NEW','RENEWALS'}

If(Match(Description, 'NEW', 'RENEWALS'),(column(2)-column(1))/fabs(column(1))*100)

If not, please let us know your first and second column of expressions?

Re: Calculation

upaliwije — Fri, 01 Sep 2017 10:59:05 GMT

in fact my expression like this

if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NEW'

,sum({<POL_TYPE={'N'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)//sum({<POL_TYPE={'N'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY = {">=1 <=14" } >}Nos)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'RENEWALS'

,sum({<POL_TYPE={'R'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NEW+RENEWAL'

,sum({<POL_TYPE={'N','R'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'ADDITIONALS'

,sum({<POL_TYPE={'A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'GROSS PREMIUM'

,sum({<POL_TYPE={'N','R','A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'REFUNDS'

,sum({<POL_TYPE={'F'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NET PREMIUM'

,sum({<RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = ''

,''

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NET NEW BUSINESS'

,sum({<POL_TYPE={'N'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)+sum({<POL_TYPE={'A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},HISTORY={'N'},RISK_DAY=$(v1h)>}Nos)+sum({<POL_TYPE={'F'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},HISTORY={'N'},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NET RENEWALS'

,sum({<POL_TYPE={'R'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)+sum({<POL_TYPE={'A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},HISTORY={'Y'},RISK_DAY=$(v1h)>}Nos)+sum({<POL_TYPE={'F'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},HISTORY={'Y'},RISK_DAY=$(v1h)>}Nos)

,if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL') = 'NET TOTAL'

,sum({<RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DAY=$(v1h)>}Nos)

)))))))))))

Re: Calculation

upaliwije — Fri, 01 Sep 2017 11:02:09 GMT

Your one works for 1st expression but not after when I add the second expression

Re: Calculation

techvarun — Fri, 01 Sep 2017 11:21:33 GMT

if(ValueList('NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','NET NEW BUSINESS','NET RENEWALS','NET TOTAL')= 'New',(column(2)-column(1))/fabs(column(1))*100,

))

Re: Calculation

Anil_Babu_Samineni — Fri, 01 Sep 2017 11:21:50 GMT

Create in script rather calculated dimension

Load * Inline [

Description

NEW

RENEWALS

NEW+RENEWAL

ADDITIONALS

GROSS PREMIUM

REFUNDS

NET PREMIUM

NET NEW BUSINESS

NET RENEWALS

NET TOTAL

];

Then use this field as dimension and create Table

Description - Dimension

Expression

Pick(Match(Description,'','',...), Sum(...), ....)

Then,

If(Match(Description, 'NEW', 'RENEWALS'),(column(2)-column(1))/fabs(column(1))*100)

Re: Calculation

upaliwije — Fri, 01 Sep 2017 11:30:29 GMT

Thanks all

I will come back to you once I update my document

Re: Calculation

rohitraut — Fri, 01 Sep 2017 13:20:20 GMT

try this

(Pick(WildMatch(Description,'NEW','RENEWALS',Decp),(column(2)-column(1))/fabs(column(1)),(column(2)-column(1))/fabs(column(1)),'0'))*100

Re: Calculation

upaliwije — Sat, 02 Sep 2017 04:56:11 GMT

Hi Anil

Thanks for your help and I have done as you explained and my output is shown below

One more thing I want to do I want to add a blank row in between NET PREMIUM and NET NEW BUSINESS rows

Pls advise me how it could be done

Re: Calculation

Anil_Babu_Samineni — Sat, 02 Sep 2017 08:23:17 GMT

Sure, Here you need to use one more space in Match and then same for Pick with simple call it as expression for that then it will works like charm.

Re: Calculation

upaliwije — Sat, 02 Sep 2017 08:44:01 GMT

You mean to say like this

pick(match(DESCRIPTION,'NEW','RENEWALS','NEW+RENEWAL','ADDITIONALS','GROSS PREMIUM','REFUNDS','NET PREMIUM','','NET NEW BUSINESS','NET RENEWALS','NET TOTAL')

,sum({<POL_TYPE={'N'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<POL_TYPE={'R'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<POL_TYPE={'N','R'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<POL_TYPE={'A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<POL_TYPE={'N','R','A'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<POL_TYPE={'F'},RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,sum({<RISK_YEAR = {$(=Max(RISK_YEAR)-1)},RISK_DATE={$(vP16)}>}Nos)

,''

How If I want to draw a straight line instead of Blank row

Re: Calculation

Anil_Babu_Samineni — Sat, 02 Sep 2017 08:46:05 GMT

Kind of thinking the same? May be share recent file? I will work for you

Re: Calculation

upaliwije — Sat, 02 Sep 2017 09:01:54 GMT

Anil

I want a line as shown below

I attach mu Qv app

Re: Calculation

Anil_Babu_Samineni — Sat, 02 Sep 2017 09:26:55 GMT

I don't think so, Because we can draw for all rows but only one row i never face this issue. But, If this report is constant you could place over Line object

PFA

Re: Calculation

upaliwije — Sat, 02 Sep 2017 09:46:32 GMT

Thanks Anil for all your help! Look forward to your help in future