5 Replies Latest reply on Dec 9, 2015 9:15 AM by siplus

    Insurance Calculation / Case/Let/If mixed

    ChristofferRexen_1

      Hello everyone,

       

      I got this tricky Calculated Field, which I have put the following Case and If statements in, based on a bunch of criteria, although I don't quite get the result I want - I still miss the third and last part of the Case statement, so bare over with me.

       

      I'm from Denmark, so a lot of the variables in the Case as well as the Let function, is danish terms, although shouldn't mean the big.


      Now the Criteria is kinda confusing, although lemme try explain:


      Talking about Insurances and insurance premiums, based on how many m3 of water the companies are pumping out, but with a minimum premium and maximum premium.

      So here goes:

       

      Water until 100.000 m3 - Costs 0,046293 DKK

      Minimum premium - 1.500 DKK

      If the water volume being pumped is less or equal 32.402,30, the premium will cost 1.500 DKK, if the water volume is above, you will have to take the m3 water volume * Costs, but the calculation must not be less than the minimum premium, 1.500 DKK

       

      Water from 100.000 m3 until 1.000.000 m3 - Costs 0,022944 DKK

      Minimum premium - 3.000 DKK

      Maximum premium - 10.000 DKK

      This is the tricky bit, I think...

      If the water volume being pumped is between 100.000 - 1.000.000 and less or equal 130.753,1380, the premium will minimum cost 3.000 DKK, if the water volume is above, you will have to take the m3 water volume * Costs, but the calculation must not be less than the minimum premium, 3.000 DKK - AND

      If the water volume being pumped is between 100.000 - 1.000.000 and more or equal 435.843,793, the premium will maximum cost 10.000 DKK, if the water volume is above, you will have to take the m3 water volume * Costs, but the calculation must not exceed the maximum premium, 10.000 DKK

       

      Water more than 1.000.000 m3 - Costs 0.011496 DKK

      Minimum premium - 10.000 DKK

      If the water volume being pumped is less or equal 869.867,780, the premium will cost 10.000 DKK, if the water volume is above, you will have to take the m3 water volume * Costs, but the calculation must not be less than the minimum premium, 10.000 DKK

       

      To make all things a lot better, I have to divide the premium calculated / minimum premium with 12 to get the monthly premium, and then multiply it with the months the insurance is agreed to run for, let's say, 3 months.

       

      In the Let:

      uvm = m3 water volume

      kvm1 = critical water volume, before minimum premium ( and so on with kvm2 etc.)

      pris1 = price for water volume ( etc. )

      mp1 = minimum premium ( etc. )

      min_uvm1 = minimum pumped water volume ( etc. )

       

      Case (

      Kunde_Police_Policetype::_PK_PolicetypeID = 80121 ; // All Risk Erhvervsforsikring

       

       

      Let (

      // Variables

       

      [

      uvm = Kunde_Police::Vandmængde Pr. Police ; // Udpumpet vandmængde

      kvm1 = 32402,307044 ; // Kritisk vandmængde før prisen ændre sig fra min. præmie til kalkuleret præmie

      kvm2.1 = 130753,138075 ; // Kritisk vandmængde punkt fra 100.000 - 1.000.000 før prisen ændre sig MIN. præmie til kalkuleret præmie

      kvm2.2 = 435843,793584 ; // Kritisk vandmængde punkt fra 100.000 - 1.000.000 før prisen ændre sig MAX. præmie til kalkuleret præmie

      kvm3 = 869867,780097 ; // Kritisk vandmængde punkt mere end 1.000.000 før prisen ændre sig

       

      pris1 = Kunde_Police_Policetype::Pris1 ; // Pris under 100.0000 m3 vand

      pris2 = Kunde_Police_Policetype::Pris2 ; // Pris mellem 100.000 - 1.000.000 m3 vand

      pris3 = Kunde_Police_Policetype::Pris3 ; // Pris over 1.000.000 m3 vand

       

       

      mp1 = Kunde_Police_Policetype::Minimumspræmie1      ; // minpræmie1

      mp2 = Kunde_Police_Policetype::Minimumspræmie2      ; // minpræmie2.1

      mp3 = Kunde_Police_Policetype::Minimumspræmie3    ; // maxpræmie2.2 / minpræmie3

       

       

      min_uvm1 = 100000 ; // MIN. udpumpet vandmængde under 100.000 m3 vand

      min_uvm2 = 1000000 // MAX. udpumpet vandmængde over 1.000.000 m3 vand

       

       

      ] ;

       

       

      // Calculation

        Case (

        uvm = 0 ; 0 ;

        If ( ( uvm > 0 and uvm < min_uvm1 and uvm < kvm1 ) ; ( ( mp1 / 12 ) * Kunde_Police::DatoPeriode ) ; ( ( ( uvm * pris1 ) / 12 ) * Kunde_Police::DatoPeriode ) ) ;

        If ( ( uvm > min_uvm1 and uvm < min_uvm2 and uvm < kvm2.1 ) ; If ( ( uvm * pris2 ) < mp2 ; ( mp2 / 12 ) * Kunde_Police::DatoPeriode ) ; ( ( ( uvm * pris2 ) / 12 ) * Kunde_Police::DatoPeriode ) ) ;

          )

      )

      )

       

      I hope you can help me, I'm a desperate man!


      If it's all cloudy, write me back, I'll try to point it more out.


      Other similar solutions, is welcome.


      /Cheers

        • 1. Re: Insurance Calculation / Case/Let/If mixed
          ChristofferRexen_1

          Sorry, forgot to mention in the above.

          If the m3 water volume is let's say, 99.000, the result and calc, would be based on


          Water until 100.000 m3 - Costs 0,046293 DKK

          Minimum premium - 1.500 DKK


          If the m3 water volume is let's say, 999.000, the result and calc, would be based on


          Water from 100.000 m3 until 1.000.000 m3 - Costs 0,022944 DKK

          Minimum premium - 3.000 DKK

          Maximum premium - 10.000 DKK


          The problem with my Case function is, that it get's into the second If statement right away, considering the water volume is 99.000 m3


          Hope you can help me out.

          • 2. Re: Insurance Calculation / Case/Let/If mixed
            Extensitech

            Overall challenges:

             

            • The case at the beginning can be merged with the case inside the Let. Won't change anything, but it would sure make things easier to read.
            • Parentheses are only required around formulas when you want the order of operations to be other than the norm. So
              • If ( ( uvm > min_uvm1 and uvm < min_uvm2 and uvm < kvm2.1 ) ; If ( ( uvm * pris2 ) < mp2 ; ( mp2 / 12 ) * Kunde_Police::DatoPeriode ) ; ( ( ( uvm * pris2 ) / 12 ) * Kunde_Police::DatoPeriode ) ) ;
                • is the same as
              • If ( uvm > min_uvm1 and uvm < min_uvm2 and uvm < kvm2.1 ; If ( uvm * pris2 < mp2 ; mp2 / 12 * Kunde_Police::DatoPeriode ) ; uvm * pris2 / 12 * Kunde_Police::DatoPeriode ;
                • but a LOT easier to read and troubleshoot
            • Case works everywhere If would, but the reverse is not true. There's no reason at all to switch from case to if and back.

             

            Specific challenge (I think):

             

            Case accepts as parameters of condition/result pairs, with an optional default result at the end. With the first "if", it appears that you're trying to calculate a result (it's not going to come out to Boolean (1/0, true/false, etc). However, the if statement is in the position of a condition parameter for the case statement. The second if is in the result position.

             

            Bottom line is that "Case/Let/If mixed", in this particular way, is unnecessarily complex. Declare your Let variables, then do a Case statement. There's no need for "if", and no reason to put a second case outside of the let.

             

            I started to clean up your calc just to be able to read it. I haven't created your final calculation formula for you. The Danish comments and definition were more challenging than I would've thought they'd be (I kept returning to your post to find out what was what). Also, there are a few areas where I can't be sure of intent. As an example, your first If starts with "uvm > 0 ", but the first (now second) case condition is "uvm = 0", which means all the zeros have been handled, and the only way "uvm > 0" could be false is if uvm is negative. Is it possible for uvm to be negative? If so, you should have a condition/result for that, but if not, then "uvm > 0" is totally unnecessary.

             

            However, I think I've found the pattern you should be using for this need, and untangled it a bit, so I'm posting that anyway.

             

            So... The "Let" statement declares the values you plan to use. So far so good.

             

            Next, in your Case statement, work through all the possible scenarios, and create a condition (a function, resulting in true or false, to determine whether this is the scenario) and a result (a function for what to return in that scenario). Remember what once the Case reaches a condition that is met, you'll get that result, and the remaining conditions are not checked.

             

            Let ( [

            uvm = Kunde_Police::Vandmængde Pr. Police // Udpumpet vandmængde

             

            ; kvm1 = 32402.307044 // Kritisk vandmængde før prisen ændre sig fra min. præmie til kalkuleret præmie

            ; kvm2.1 = 130753.138075 // Kritisk vandmængde punkt fra 100.000 - 1.000.000 før prisen ændre sig MIN. præmie til kalkuleret præmie

            ; kvm2.2 = 435843.793584 // Kritisk vandmængde punkt fra 100.000 - 1.000.000 før prisen ændre sig MAX. præmie til kalkuleret præmie

            ; kvm3 = 869867.780097 // Kritisk vandmængde punkt mere end 1.000.000 før prisen ændre sig

             

            ; pris1 = Kunde_Police_Policetype::Pris1 // Pris under 100.0000 m3 vand

            ; pris2 = Kunde_Police_Policetype::Pris2 // Pris mellem 100.000 - 1.000.000 m3 vand

            ; pris3 = Kunde_Police_Policetype::Pris3 // Pris over 1.000.000 m3 vand

             

            ; mp1 = Kunde_Police_Policetype::Minimumspræmie1    // minpræmie1

            ; mp2 = Kunde_Police_Policetype::Minimumspræmie2    // minpræmie2.1

            ; mp3 = Kunde_Police_Policetype::Minimumspræmie3    // maxpræmie2.2 / minpræmie3

             

            ; min_uvm1 = 100000 // MIN. udpumpet vandmængde under 100.000 m3 vand

            ; min_uvm2 = 1000000 // MAX. udpumpet vandmængde over 1.000.000 m3 vand

             

             

            ] ;

             

            Case (

             

                 Kunde_Police_Policetype::_PK_PolicetypeID <> 80121 // All Risk Erhvervsforsikring // CONDITION

                ; "" // RESULT

             

                ; uvm = 0 // CONDITION

                  ; 0 // RESULT

             

             

                ; uvm > 0 and uvm < min_uvm1 and uvm < kvm1 // CONDITION??

                ; mp1 / 12 * Kunde_Police::DatoPeriode ; uvm * pris1 / 12 * Kunde_Police::DatoPeriode  // RESULT??

             

                ; // NEXT CONDITION

                ; // NEXT RESULT

             

                ; // DEFAULT RESULT

            )

             

            )

             

             

            HTH

            Chris Cain

            Extensitech

            • 3. Re: Insurance Calculation / Case/Let/If mixed
              RubenVanDenBoogaard

              how about:

               

              Let([

              uvm = Volume;

              pris = Case(uvm < 100000 ; ,046293 ; uvm < 1000000 ; ,022944 ; ,011496);

              min = Case(uvm < 100000 ; 1500 ; uvm < 1000000 ; 3000 ; 10000);

              p= uvm * pris

              ];

               

               

              Case ( p < min ; min ; p > 10000 ; 10000 ; p)

               

               

              )

              • 4. Re: Insurance Calculation / Case/Let/If mixed
                dtcgnet

                Your case statement, because of the compounded IF statements, needs to be reworked.

                 

                Keep in mind that each "line" of a Case statement has two components.

                Component 1 is the test. It will be evaluated. If the test returns "true", then the Case statement will return whatever Component 2 evaluates as.

                Component 2 is the result that you want if the test (Component 1) is true.

                 

                Case (

                Component1.1 [Test1] ; Component2.1 [Result1] ;

                Component1.2 [Test2] ; Component2.2 [Result2] ;

                Component1.3 [Test3] ; Component2.3 [Result3] ;

                "Default answer if none of the first three lines is true"

                )

                 

                In your Case statement, you think you have three conditions, but you don't. You have two.

                 

                Component 1 in the first line of your Case statement is "uvm = 0". Component 2 is 0. If uvm isn't 0, processing goes to your second line.

                 

                Component 1 in your second line is THE RESULT of the If statement. What is Component 2? Well...Component 2 is whatever comes after the ";" that occurs at the end of your If statement. Since Component 1 WILL return a result, then the line will evaluate as True, so line 2 is the part of your Case statement that will ALWAYS be used to return your answer. Your answer will be the result of the nested IF that you THINK is the third line of your Case statement:

                 

                If ( ( uvm > min_uvm1 and uvm < min_uvm2 and uvm < kvm2.1 ) ; If ( ( uvm * pris2 ) < mp2 ; ( mp2 / 12 ) * Kunde_Police::DatoPeriode ) ; ( ( ( uvm * pris2 ) / 12 ) * Kunde_Police::DatoPeriode ) )

                 

                I imagine you are ALWAYS getting 1 of 2 answers. You are ALWAYS getting either:

                1) mp2 / 12 * DatoPeriode

                OR

                2) uvm * pris2 / 12 * DatoPeriode

                • 5. Re: Insurance Calculation / Case/Let/If mixed
                  siplus

                  as a general remark, also remember that max(1000, value) will return 1000 if value < 1000 and value otherwise.