AnsweredAssumed Answered

Insurance Calculation / Case/Let/If mixed

Question asked by ChristofferRexen_1 on Dec 9, 2015
Latest reply on Dec 9, 2015 by siplus

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

Outcomes