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

[techvarun]

try like below

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

[techvarun]

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.

[effinty2112]

Hi Upali,

What about:

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

Cheers

Andrew

[Anil_Babu_Samineni]

Better, If you provide sample application?

Perhaps this?

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

OR

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?

[upaliwije]

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)

)))))))))))

[upaliwije]

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

[techvarun]

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,

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,

))

[Anil_Babu_Samineni · Accepted solution]

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)

[upaliwije]

Thanks all

I will come back to you once I update my document