Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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