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

    FirstSortedValue and Set Analysis

    Chris Weldon

      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
          Shraddha Gajare

          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
              Chris Weldon

              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
              Sunny Talwar

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

                • Re: FirstSortedValue and Set Analysis
                  Chris Weldon

                  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

                      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
                          Chris Weldon

                          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