Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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

View solution in original post

6 Replies
swuehl
MVP
MVP

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
Author

Thanks plenty for your help!

That's exactly what I was looking for.

Regards,

Nathan

qlikpahadi07
Specialist
Specialist

Hi Nathan,

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

Not applicable
Author

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
Author

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

Thanks Nathan,

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