Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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
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
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

Thanks plenty for your help!

That's exactly what I was looking for.

Regards,

Nathan

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

qlikpahadi07
Valued Contributor

Re: Ratios of Current vs Previous Years

Thanks Nathan,

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

Community Browser