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: 
Anonymous
Not applicable

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

12 Replies
Anonymous
Not applicable
Author

Sunny,

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

This is the table now:

AgeTable.jpg

This is the formula for the Qx column

Formula.jpg

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:

Mortality.jpg

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

Formula2.png

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

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

Cheers

Chris

sunny_talwar

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.

Anonymous
Not applicable
Author

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