6 Replies Latest reply: Dec 23, 2013 11:21 PM by Rohit Koul

# Ratios of Current vs Previous Years

Hi All,

I need to put in a formula which is the ratio of profits from the current year vs the previous year in a straight table for all underwriting years (which is the dimension in the straight table).

My current formula looks like:

Sum({\$<PLR_PLY_TRANSACTIONTYPE = {'*'}-{'Q'},UnderwritingYear={'UnderwritingYear'}>}PLR_T_COYPREM) / Sum({\$<PLR_PLY_TRANSACTIONTYPE = {'*'}-{'Q'},UnderwritingYear={'UnderwritingYear-1'}>}PLR_T_COYPREM)

This however doesn't seem to be generating anything in my straight table except for a series of NULL values.

I'm guessing there's something wrong with the syntax I'm using.

Any help is appreciated.

Regards,

Nathan

• ###### Re: Ratios of Current vs Previous Years

The set expression is evaluated once per chart, not per dimension value, so you can't reference the dimension like this.

Use chart inter record functions like above() to achieve what you want:

Sum({\$<PLR_PLY_TRANSACTIONTYPE = {'*'}-{'Q'}>}PLR_T_COYPREM) /

above(Sum({\$<PLR_PLY_TRANSACTIONTYPE = {'*'}-{'Q'} >}PLR_T_COYPREM))

• ###### Re: Ratios of Current vs Previous Years

That's exactly what I was looking for.

Regards,

Nathan

• ###### Re: Ratios of Current vs Previous Years

Hi Nathan,

Just Interested in what does this {'*'}-{'Q'} do in Set Analysis ?

• ###### Re: Ratios of Current vs Previous Years

Hi,

Sure, the 'Q' is just the indicator for a quote and so the command {'*'}-{'Q'} selects all transactions that aren't quotes.

Don't know if there's another way, but this seems to work alright.

Regards,

Nathan

• ###### Re: Ratios of Current vs Previous Years

You can use below expression to remove the PLR_PLY_TRANSACTIONTYPE having 'Q':

Sum({\$<PLR_PLY_TRANSACTIONTYPE -= {'Q'}>}PLR_T_COYPREM) /

above(Sum({\$<PLR_PLY_TRANSACTIONTYPE -={'Q'} >}PLR_T_COYPREM))

• ###### Re: Ratios of Current vs Previous Years

Thanks Nathan,

Yes I normally use '-=' ... anyways great learning thanks