7 Replies Latest reply on Jan 26, 2017 4:23 AM by ChristofferRexen_1

    Mean Calculation, Need Guidance!

    ChristofferRexen_1

      So, got this mean calculation.

      Only thought it was meant to be build to last a year, but since the client is so thrilled about the system, they wanted me to perfect the damn calculation -- oh bugger..!

       

      Now, the calc below does work.

      Although the problem comes up, when I try making a CASE statement, in the below calculation based on a Date field, since the calc below was only meant to be used in the year 2016.

      Now we are in 2017, with a new tariff, prices, terms etc. etc. etc.

       

      Now, for not ruining any existing data, I thought I could "just" implement a Case statement, which would test the two Date Fields called "Fornyelsesdato" and "DatoSlut__lcd Korrekt".

       

      If these were to be in 2016, it should use the calc below with 2016 figures.

      If these were to be in 2017, it should use the calc further below with 2017 figures.

       

      Although, when trying to insert the Case statement, the field just keeps blank.

      What in the world am I doing wrong - i figured this would be a lot more simple?

       

      Case (

       

      Year ( Fornyelsesdato ) = Year ( 2016 ) ;

       

      // 2016 Figures

      Case (

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80121 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_AllRiskErhverv_Tarif2016 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80122 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_ErhvervProduktansvar_Tarif2016 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80123 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Arbejdsskadeforsikring_Tarif2016 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80124 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_UdvidetGrundejeransvar_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80125 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_UdvidetGravedækning_Tarif2016 ( KUN_Kunde_Police::AntalForsikrede ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80126 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Retshjælp_Tarif2016 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80127 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Ulykkesforsikring_Tarif2016 ( AntalBestyrelser ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80128 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80129 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80130 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80131 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80132 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80133 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80134 ; DV_Præmie_Entrepriseforsikring_Tarif2016 ( Entreprisesum ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80136 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80137 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80138 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2016 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) )

       

      ;

       

      Year ( Fornyelsesdato ) = Year ( 2017 ) ;

       

      // 2017 Figures

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80121 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_AllRiskErhverv_Tarif2017 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80122 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_ErhvervProduktansvar_Tarif2017 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80123 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Arbejdsskadeforsikring_Tarif2017 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80124 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_UdvidetGrundejeransvar_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80125 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_UdvidetGravedækning_Tarif2017 ( KUN_Kunde_Police::AntalForsikrede ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80126 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Retshjælp_Tarif2017 ( KUN_Kunde_Police::Vandmængde ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80127 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Ulykkesforsikring_Tarif2017 ( AntalBestyrelser ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80128 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80129 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80130 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80131 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80132 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80133 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Bestyrelsesansvarsforsikring_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80134 ; DV_Præmie_Entrepriseforsikring_Tarif2017 ( Entreprisesum ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80136 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80137 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) ) ;

       

      KUN_Kunde_Police_Policetype::__kp_PolicetypeID = 80138 ; DV_UdregnPeriodiskPræmieDays ( DV_Præmie_Cyber Risk_Tarif2017 ( _kf_PolicetypeID ) ; DV_DateRange ( Fornyelsesdato ; DatoSlut__lcd Korrekt ; "d" ) )

      )

       

      The above consists of a lot of unique Custom Functions.

      DV_UdregnPeriodiskPræmieDays

      DV_DateRange

      And every other function with DV.

       

      Could someone please direct me in the correct direction, i would be sincerely grateful.

       

      P.S. I thought if it was possible to even make a Let statement out of the above?

       

      Regards

      CR

        • 1. Re: Mean Calculation, Need Guidance!
          Philip_Jaffe

          First thing to try is this:

          Change

           

          Year ( Fornyelsesdato ) = Year ( 2016 )

           

          to

           

          Year ( Fornyelseadato ) = 2016

           

          or

           

          Year ( Fornyelseadato ) = 2017

           

          as the case may be.

           

          If you can, adjust your custom function(s) to allow for inputing a "year" parameter.  Then you need only write the calculation once, providing whatever year is currently needed.

          1 of 1 people found this helpful
          • 2. Re: Mean Calculation, Need Guidance!
            BruceRobertson

            "What in the world am I doing wrong ?"

             

            Using a calculation.

            Do not use a calculation.

            All those choices belong in a related table.

            Next year you update the data in the table.

            1 of 1 people found this helpful
            • 3. Re: Mean Calculation, Need Guidance!
              mikebeargie

              0_o...

               

              About lookups

               

              I would sincerely consider just scrapping the whole thing and creating a table, related by:

              KUN_Kunde_Police_Policetype::__kp_PolicetypeID

              and a calculated "year" field of: Year ( Fornyelsesdato )

               

              Then perform a looked up value from that table to grab the value that matches those two criteria. That would take care of 99% of your issues, and allow you to continue to add rate charts for future years without having to have ridiculous calculations.

               

              Otherwise, what would this look like in 4 years? 10 years?

              1 of 1 people found this helpful
              • 4. Re: Mean Calculation, Need Guidance!
                Philip_Jaffe

                Agreed.  Ultimately that is the way to go.

                 

                But for now if you just need to get that script working ASAP (since it's January 2017 already), your case statement isn't working because Year ( DateField ) = 2017 or 2016 etc...  Year ( 2017 ) = 6 , Year (2016) = 6.  2017 ≠ 6.  2016 ≠ 6.  Therefore your Case statement skips ALL your calculations in both instances.

                2 of 2 people found this helpful
                • 5. Re: Mean Calculation, Need Guidance!
                  ChristofferRexen_1

                  BruceRobertson wrote:

                   

                  "What in the world am I doing wrong ?"

                   

                  Using a calculation.

                  Do not use a calculation.

                  All those choices belong in a related table.

                  Next year you update the data in the table.

                  As mentioned, only ment to be in function for a year.

                  Although things changed.

                  • 6. Re: Mean Calculation, Need Guidance!
                    ChristofferRexen_1

                    Mike Beargie wrote:

                     

                    0_o...

                     

                    About lookups

                     

                    I would sincerely consider just scrapping the whole thing and creating a table, related by:

                    KUN_Kunde_Police_Policetype::__kp_PolicetypeID

                    and a calculated "year" field of: Year ( Fornyelsesdato )

                     

                    Then perform a looked up value from that table to grab the value that matches those two criteria. That would take care of 99% of your issues, and allow you to continue to add rate charts for future years without having to have ridiculous calculations.

                     

                    Otherwise, what would this look like in 4 years? 10 years?

                    Thanks Mike, always a pleasure hearing your thoughts.

                     

                    I'll go with Judd's option for now since I am a little behind, and later your suggested Look Ups alternative.

                    • 7. Re: Mean Calculation, Need Guidance!
                      ChristofferRexen_1

                      Judd45 wrote:

                       

                      Agreed. Ultimately that is the way to go.

                       

                      But for now if you just need to get that script working ASAP (since it's January 2017 already), your case statement isn't working because Year ( DateField ) = 2017 or 2016 etc... Year ( 2017 ) = 6 , Year (2016) = 6. 2017 ≠ 6. 2016 ≠ 6. Therefore your Case statement skips ALL your calculations in both instances.

                      Thanks Judd, that did the tricks.

                      Going with your option now, and later making it a Look Up value.

                       

                      Cheers!