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

# Insurance Calculation / Case/Let/If mixed

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

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

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

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

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

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

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

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

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

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

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