12 Replies Latest reply: Nov 10, 2016 4:36 PM by Chris Weldon

FirstSortedValue and Set Analysis

Folks,

I am struggling with syntax (I think!).

I am trying to look up the age for a particular Qx.  The data is stored like this.

Member AgeQxROWMale

40

0.545
410.609
420.685
430.772

I am calculating the weighted Qx in a calculation and then want to look up the age.   I have the two parts working separately.

I calculate the weighted Qx like this:

(sum({<TrackedQuote={'Yes'},QuoteType-={'Research'},Benefit={'GL'},QuoteStatus={'Issued','Finalised'},

DateQuoteIssued={">=\$(=date(vStartDate,'DD/MM/YYYY'))<=\$(=date(vEndDate,'DD/MM/YYYY'))"}>}QxROWMale*SumAssuredUSD)/

sum({<TrackedQuote={'Yes'},QuoteType-={'Research'},Benefit={'GL'},QuoteStatus={'Issued','Finalised'},

DateQuoteIssued={">=\$(=date(vStartDate,'DD/MM/YYYY'))<=\$(=date(vEndDate,'DD/MM/YYYY'))"}>}SumAssuredUSD))

(This calculated the SumAssured * the mortality rate and then divides by the Sum Assured for the given quotes)

I can do the look up if I used a fixed value to look up the age.

firstsortedvalue(MemberAge,aggr(sum({<QxROWMale={'>1.05'}>}QxROWMale),QxROWMale,Region))

Both of these work fine - but I am struggling to combine the two things together.  So I need to replace the 1.05 with the answer to the weighted Qx.  How would I do that?  I have tried this, but it doesn't seem to work?

firstsortedvalue(MemberAge,aggr(sum({<QxROWMale={'>\$(=(sum({<TrackedQuote={'Yes'},QuoteType-={'Research'},Benefit={'GL'},QuoteStatus={'Issued','Finalised'},
DateQuoteIssued={">=\$(=date(vStartDate,'DD/MM/YYYY'))<=\$(=date(vEndDate,'DD/MM/YYYY'))"}>}QxROWMale*SumAssuredUSD)/
sum({<TrackedQuote={'Yes'},QuoteType-={'Research'},Benefit={'GL'},QuoteStatus={'Issued','Finalised'},
DateQuoteIssued={">=\$(=date(vStartDate,'DD/MM/YYYY'))<=\$(=date(vEndDate,'DD/MM/YYYY'))"}>}SumAssuredUSD)))'}>}QxROWMale),QxROWMale,Region))

Anyone able to help?

Chris

• Re: FirstSortedValue and Set Analysis

Try using variable for this.

Store weighted Qx Expression into a variable and then use that variable at the place of 1.05

firstsortedvalue(MemberAge,aggr(sum({<QxROWMale={">\$(=vVariable)"}>}QxROWMale),QxROWMale,Region))

• Re: FirstSortedValue and Set Analysis

I need this to be at a region level so I can show the data as follows:

Region            Average Age        Weighted Age       Qx Weighted Age

Asia                         38.1                    39.8                         40.6

Middle East             36.2                    37.7                          39.1

Europe                    40.9                    42.3                          44.1

I have the first 2 columns, and it is the last one I am working on.  Unfortunately, the way the calculation works is not a simple aggregation of the data - you need to work out the Sum Assured * Mortality and then divide by the Sum Assured to get the average mortality rate, then look up that rate in a table to get the age.  As I said above I can do each bit in turn, but when I bring them together I get in to trouble!

Can a variable be used if it is split by a dimension, or is it a single value?

Many thanks

Chris

• Re: FirstSortedValue and Set Analysis

variable will b a value calculated according to the region value in 1st column and that value will be used in first sorted expression.

• Re: FirstSortedValue and Set Analysis

Thanks for your help so far...getting closer.

The variable is defined and used in the table below to show the 'SA and Qx' column,

but when added to the Qx column is returning nulls.

The formula is now:

firstsortedvalue(MemberAge,aggr(sum({<QxROWMale={'>\$(=vWeightedQx)'}>}QxROWMale),QxROWMale,Region))

I have tried with both single and double quotes but neither work.

Any ideas?

Cheers

Chris

• Re: FirstSortedValue and Set Analysis

Have you checked whether variable is returning correct value?

• Re: FirstSortedValue and Set Analysis

Thanks for your response.  Yes the variable is working fine - I have used it to populate the column "SA and Qx" above, and I have spot checked some of the values and they seem to be correct.

But when I spot the variable in the formula the column "Qx" seems to be returning nulls as above?

Cheers

Chris

• Re: FirstSortedValue and Set Analysis

Would you be able to share a sample with the expected output you are looking to get?

• Re: FirstSortedValue and Set Analysis

Sunny,

What I am trying to do is use the variable that results in column "SA and Qx" in the table above, and look up the highest age over that amount from a table that is effectively :

MemberAge           QxROWMale

45                           0.968

46                           1.069

47                           1.181

48                           1.271

So for Hong Kong, where I have a variable answer of 1.11 I need to return the MemberAge 47.  In the case of Singapore in the table above, I need to return 48.

All suggestions welcome!

Cheers

Chris

• Re: FirstSortedValue and Set Analysis

Since you are doing a row by row comparison, I feel like you would need to use Aggr() function instead of using set analysis because set analysis is evaluated once per chart. It won't be able to pick 1.11 for Hong Kong and 1.26 for Singapore.

May be like this:

Min(Aggr(If(QxROWMale >= vVariable, MemberAge), Area, Region, MemberAge))

• Re: FirstSortedValue and Set Analysis

Sunny,

Getting closer but not quite there.  Many thanks for your help so far.

This is the table now:

This is the formula for the Qx column

It looks like it is returning the first non 0 age from the mortality table.  Here is a temp table I created showing the data:

The variable vWeightedQx appears to be working as it forms the column Qx SA in the first table, where the formula is:

But not sure it works as part of the formula you suggested.

Am I missing something obvious?  Getting frustrated with this now!

Cheers

Chris

• Re: FirstSortedValue and Set Analysis

Maybe if you share a sample it might get easier for us to offer help here. I am not sure what might not be working here, but may be looking at the sample might help me figure it out.

• Re: FirstSortedValue and Set Analysis

Sunny,

Thanks for the offer of help.

Here is a simplified version that suffers from the same issue.  The selection criteria is much simpler, so it is not obvious a variable is needed in this version, but in the real thing it is much more complicated.

Cheers

Chris