9 Replies Latest reply on Oct 2, 2012 2:12 PM by comment

    Two FM Pro 12 Calculation questions

    tnorling

      ALCON

       

      I have two calculation questions about a Sevice Ticket Database I have created.

       

      1. In the database there are fields called "DateCreated", "DateClosed" and "Completion Time". I would like the Completion Time to calculate the amount of days between the two Created and Closed Dates. And also have the completion time remain blank if there is not a closed date inputed yet.

       

      I found online one calculation that kind of works but has a strange number value in the fields if the date closed has not been provided.

       

      ________

       

      Int((DateClosed - DateCreated)/7) * 5 + If(DayOfWeek(DateClosed) < DayOfWeek(DateCreated);

      Min(5; DayOfWeek(DateClosed) - 1) + Max(0; 6-DayOfWeek(DateCreated));

      If(DayOfWeek(DateCreated) < 7; Min(6;DayOfWeek(DateClosed)) -

      DayOfWeek(DateCreated); 0)) + 1

       

      _______

       

       

       

       

      2. Based on the answer for "Completion Time" I want the "SLA Time Met" Field to say "Yes" or "No" and the Text to be Green (Yes) and Red (No). Below is what I have so far.

       

       

      _______________

       

      Case(Completion Time <= 4;"Yes";"No")

       

      _______________

       

      Thank you for your help!

       

      Terry

        • 1. Re: Two FM Pro 12 Calculation questions
          comment

          tnorling wrote:

           

          I would like the Completion Time to calculate the amount of days between the two Created and Closed Dates. And also have the completion time remain blank if there is not a closed date inputed yet.

           

          Try =

           

          Case ( DateClosed ; DateClosed - DateCreated )

          1 of 1 people found this helpful
          • 2. Re: Two FM Pro 12 Calculation questions
            tnorling

            Michael,

             

            That worked great for the Dates calcuations. I did add Case ( DateClosed ; DateClosed - DateCreated ) + 1, so it would add the actual day if the work was completed the same day. Other then that it works great and cleaned up the formula alot.

            Thanks

             

            Any thoughts on the second color question?

            • 3. Re: Two FM Pro 12 Calculation questions
              comment

              tnorling wrote:

               

              I did add Case ( DateClosed ; DateClosed - DateCreated ) + 1, so it would add the actual day if the work was completed the same day.

               

              I believe it needs to be =

               

              Case ( DateClosed ; DateClosed - DateCreated + 1 )

               

              Otherwise you'll get 1 instead of blank when DateClosed is empty.

               

               

               

              tnorling wrote:

               

              Any thoughts on the second color question?

               

              I am not sure I understand the second question. Do you really need a calculation field for this - or would a conditionally formatted text  be sufficent?

              1 of 1 people found this helpful
              • 4. Re: Two FM Pro 12 Calculation questions
                BruceHerbach

                Terry,

                 

                You might find this a bit easier. 

                 

                Let(

                days = Case(

                isempty(DateClosed); "";

                getasnumber( Date (Month(DateClosed); Day(DateClosed); Year(DateClosed)) - getasnumber(Date(Month(DateCreated); Day(DateCreated); Year(DateCreated)))

                 

                days))

                 

                This will be empty if the DateClosed field is empty and a number 0 or greater if the field is populated.  Value will be the number of days the ticket was open.

                 

                For the SLA Tme Met, you can use conditional formatting to set the highlight based on the Value in the field.   Do you want it to be No if it is more then 4 days and the Completetion time is still blank?  You could include an isempty(Completion Time) in the conditional formatting and turn the field backgrond red with no value in the field.

                 

                HTH

                Bruce

                • 5. Re: Two FM Pro 12 Calculation questions
                  tnorling

                  Bruce

                   

                  Thanks for the help. Though I did use Mike's suggestion for part 1, but your answer for part 2 about the conditional formatting worked great. I have been pulling out my hair for several hours over all this and in less then a half hour of my post to the forum I had all my questions answered. Very cool of you guys. Cheers

                  • 6. Re: Two FM Pro 12 Calculation questions
                    comment

                    Bruce Herbach wrote:

                     

                    Date (Month(DateClosed); Day(DateClosed); Year(DateClosed))

                     

                    ???

                    • 7. Re: Two FM Pro 12 Calculation questions
                      BruceHerbach

                      Terry,

                       

                      There are a lot of helpful people here.  Glad you were able to get this worked out.

                       

                      Bruce

                      • 8. Re: Two FM Pro 12 Calculation questions
                        BruceHerbach

                        Michael,

                         

                        You are right, the Date function wasn't necessary.

                         

                        If a ticket is closed on the day it was opened,  should the Completion Time be 0 or 1?

                         

                        Bruce

                        • 9. Re: Two FM Pro 12 Calculation questions
                          comment

                          Bruce Herbach wrote:

                           

                          the Date function wasn't necessary.

                           

                          Nor are the IsEmpty() or GetAsNumber() manipulations. If you compare your version with mine (in the first reply of this thread), you'll see that they are equivalent - except  you took a very scenic route...

                           

                           

                          If a ticket is closed on the day it was opened,  should the Completion Time be 0 or 1?

                           

                          1, says Terry in reply #2.