# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

## 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

1 Solution

Accepted Solutions
MVP

## 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))

6 Replies
MVP

## 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))

Not applicable

## Re: Ratios of Current vs Previous Years

That's exactly what I was looking for.

Regards,

Nathan

Valued Contributor

## Re: Ratios of Current vs Previous Years

Hi Nathan,

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

Not applicable

## 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

Not applicable

## 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))

Valued Contributor

## Re: Ratios of Current vs Previous Years

Thanks Nathan,

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