topic Re: FirstSortedValue and Set Analysis in App Development

FirstSortedValue and Set Analysis

Anonymous — Thu, 03 Nov 2016 14:13:35 GMT

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 Age	QxROWMale
40	0.545
41	0.609
42	0.685
43	0.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

shraddha_g — Fri, 04 Nov 2016 03:59:01 GMT

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

Anonymous — Fri, 04 Nov 2016 09:34:20 GMT

Shraddha,

Many thanks for your reply.

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

shraddha_g — Mon, 07 Nov 2016 06:56:51 GMT

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

Anonymous — Mon, 07 Nov 2016 17:58:35 GMT

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

shraddha_g — Tue, 08 Nov 2016 11:46:06 GMT

Have you checked whether variable is returning correct value?

Re: FirstSortedValue and Set Analysis

sunny_talwar — Tue, 08 Nov 2016 11:51:12 GMT

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

Re: FirstSortedValue and Set Analysis

Anonymous — Tue, 08 Nov 2016 13:00:02 GMT

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

Anonymous — Tue, 08 Nov 2016 13:05:04 GMT

Sunny,

Thanks for your response.

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

sunny_talwar — Tue, 08 Nov 2016 15:04:05 GMT

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

Anonymous — Wed, 09 Nov 2016 19:39:53 GMT

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

sunny_talwar — Thu, 10 Nov 2016 01:53:38 GMT

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

Anonymous — Thu, 10 Nov 2016 21:36:39 GMT

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