Calculation

[upaliwije]

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

[rohitraut]

try this

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

[upaliwije]

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

[Anil_Babu_Samineni]

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.

[upaliwije]

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

[Anil_Babu_Samineni]

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

[upaliwije]

Anil

I want a  line as shown below

I attach mu Qv app

[Anil_Babu_Samineni]

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

[upaliwije]

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