11 Replies Latest reply on Sep 26, 2013 2:57 PM by Jonathan Wilkendorf

    Chain-ladder table calculation - Question 2

    kettan

      This question is a continuation of   Chain-ladder table calculation - Question 1

       

       

      Question:

      What table calculations will give us the numbers marked in blue below?

       

      See

      (1) correct chain-ladder formulas in attached Excel file and

      (2) the chain-ladder factors per development year in attached Tableau file.

       

      chain-ladder table calculation - Q2.png

        • 1. Re: Chain-ladder table calculation - Question 2
          Dan Huff

          Johan--

           

          You are killing me mate . This was even harder than the first. Before I get into the specifics, I want to be very clear that what Tableau is doing here is amazing. Tableau is generating the excel based report dynamically without having to create cell level formulas at each point. Because of this, however, a lot of calcs are needed. There may even by points in this that could be optimized or removed but I have it working so I have not played with it too much once I got it right.


          Please do not let the complexity of this drive you away from Tableau. These are incredibly hard things to build dynamically. I would rather suffer through this once in Tableau than have to do this every time I get new data in excel.

           

          The attached has the numbers correct. The 97.86 was particularly hard because my running product calc multiplied all the factors together by that point giving me an incorrect number. To fix this, I had to adjust that number by dividing it by the ratio number (1.1) that we did not want to include. If you need an explanation as how this works I am happy to try but will take some time.

           

          Regardless, here is the answer. All of the calcs needed for this part are labeled as Q2. They also rely on the Q1 calcs for this all to work together.

           

          Hope this helps,

           

          Dan

          1 of 1 people found this helpful
          • 2. Re: Chain-ladder table calculation - Question 2
            kettan

            Dan, thanks for the great effort.

             

            I am not sure if this is correct, because numbers are wrong (unchanged) in non-data area of the running total.

             

            I will not leave Tableau if this doesn't works, but sure it would impress if IBNR triangles based on the chain-ladder and other methods can be made in Tableau.

             

            Question:

            Is it possible to put the final figures in the lower triangle without data?

             

            chain-ladder table calculation - Q2 - reply.png

            • 3. Re: Re: Chain-ladder table calculation - Question 2
              Dan Huff

              Johan--

               

              Did you change anything in the workbook after opening it?

               

              Attached is what I see when I open my workbook.

               

              What I See.png

               

              Dan

              1 of 1 people found this helpful
              • 4. Re: Chain-ladder table calculation - Question 2
                kettan

                First, I am very happy to see that it works on your computer!

                No, I didn't change anything. This is a screenshot of what I see on my computer:

                chain-ladder table calculation - Q2 - reply 2.png

                 

                Ps. I go offline now with confidence that this is solved, and therefore also marked your answer as correct.

                • 5. Re: Re: Re: Chain-ladder table calculation - Question 2
                  kettan

                  Here is the workbook I looked at on my computer without seeing correct results.

                  I assume it is the same that gives correct results on your end.

                  I am curious if this re-attached workbook still shows correct figures on your computer.

                  If so, it might be because I need to upgrade from 8.0.2 to 8.0.3.

                  • 6. Re: Chain-ladder table calculation - Question 2
                    Dan Huff

                    I will test this later in the afternoon Johan but it looks like the workbook is hitting a bug that exists in 8.0.2 and 8.0.3. My preview build of 8.0.4 does not hit it.

                     

                    I will be sure to verify this but you may have to wait for 8.0.4 for this to function. I think I know what bug we are hitting so I will do some research on that as well.

                     

                    Sorry for the inconvenience,

                     

                    Dan

                    1 of 1 people found this helpful
                    • 7. Re: Chain-ladder table calculation - Question 2
                      kettan

                      Thank you, Dan, for creating these formulas for me. Hopefully they will be useful for others too, and attract more customers from the insurance industry.

                       

                      I look forward to use your formulas in production when version 8.0.4 is released.

                       

                      Jóhan

                      • 8. Re: Re: Chain-ladder table calculation - Question 2
                        Dan Huff

                        Good news Johan--

                         

                        It does appear that the issue with the workbook should be resolved in our 8.0.4 release. Sorry about the temporary inconvenience mate. If it is still not functioning on your end when you upgrade to 8.0.4, please do open a support case.

                         

                        Hope this helped,

                         

                        Dan

                        2 of 2 people found this helpful
                        • 9. Re: Chain-ladder table calculation - Question 2
                          Jonathan Wilkendorf

                          Dan,

                          Thank you for that amazing solution.  Would you mind giving me a much simpler version for how to do this calculation in Tableau that I can do so easily in Excel?  I have searched for days in the Forums and have not yet found an answer to this that works.  ( I have tried PREVIOUS_VALUE and LOOKUP functions as you can see at http://public.tableausoftware.com/views/TestSeparate/Test3?:embed=y&:display_count=no )   Your solution for Johan seems to imbed this type of need within Johan's complex example, but I am having difficulty parsing out the logic that I need from your solution.  In my example below, I intend to have a parameter control for the user to select the percentage rate so they can see how the future projection would look with that rate, while also still displaying the historical values in the same view.  The real historical data I will use is highly variable/chaotic so the Tableau 8.0 forecasting functionality is not available as an option and is not what I need in any case.

                          Thanks!

                          Jonathan

                          Test.png

                          • 10. Re: Chain-ladder table calculation - Question 2
                            Dan Huff

                            I'm sorry I missed this email for so long Jonathan. Could you try something along the lines of the following calculation:

                             

                            IF [Type]="Projection"

                            THEN sum(Amount) * (1 +window_max(max(Rate)))

                            ELSE Null

                            END

                             

                            This should allow you to grab that 10% for each and every row needed.

                            • 11. Re: Chain-ladder table calculation - Question 2
                              Jonathan Wilkendorf

                              Dan,

                              Yes, that is helpful.  Thanks!

                              Jonathan