4 Replies Latest reply on Jun 18, 2012 4:13 PM by yomango

    Time calculation



      Time calculation


      Hi, out there. I have a start time, time field. I have 5 networks that have different peak/off peak hours. When I get each network report I´d like to know, through a calculating field,  if the start time is a peak or an off peak hour. For intance, from 7 to 20 hours is Peak for some networks, for others the peak hours are between 7 and 19 hours. I´ve use the Case and If functions with AND/OR operands but they do not work all the time, if I get right the Peak hours, my Odd Peak hours get messed up and viceversa. The fields are: start_time (time field) and the network field (text field). Any one out there than can help. I will really appreciate your help.

        • 1. Re: Time calculation

          When you say "7 to 20 hours", do you mean 07:00 to 20:00 represents the peak hours time range?

          How do you keep track of the time range of peak hours for each network?

          Do you have a table, NetworkPeaks, with fields like this?

          Start Peak Time
          End Peak Time

          If so, you can establish this relationship

          Yourtable::Newtork = NetworkPeaks::network

          and this calculation will identify if a given record in your current table has a start time that falls in the peak hours range:

          If (Start_Time >= NetworkPeaks::Start Peak Time AND Start_Time <= NetworkPeaks::End Peak Time ; "Peak Hours" ; "Off Peak Hours" )

          I'm basing this solely on the two fields you've indicated that you have in each record: network and Start_time. If you also have and End_time--which might fall outside the peak hours interval, then this becomes a much more complex issue.

          • 2. Re: Time calculation

            Thank you very much for taking time to answer. No, I do not have a table with peak/off peak hours for each of the networks. It is a good  suggestion that I will consider as I polish the file,k specially if more and more networks are included. Meanwhile, I think I solved this calculated field:

            I had to include a new calculation field (start time_leftvalue)= left(start_time;2) to get the first two digits from the time stamp field that I get from each network report: start_time=Middle ( _Start_Date_and_time ; 14 ; 8 ) 

            Then I wrote the calculation to get the peak/off peak hours: and it is working.

             Case(start time_leftvalue<7;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and start time_leftvalue > 20;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and start time_leftvalue   ≤   20;"Peak";_Incarrier  ≠  "Telkom SA (Ltd)" and start time_leftvalue   ≤   19;"Peak";"Off Peak")

            One thing that was throwing me off is that the Case calculation field wasn´t working properly when the formula was:

             Case(left(start_time;2)<7;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and left(start_time;2) > 20;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and left(start_time;2)  ≤   20;"Peak";_Incarrier  ≠  "Telkom SA (Ltd)" and left(start_time;2)   ≤   19;"Peak";"Off Peak").

            I do not understand why, if the left(start_time;2) in the latter formula does the same as the calculated field start time_leftvalue. but it wasn´t working this way.

            Now it is working, thank god. I will see to your suggestion as well.


            Thank you again. as always you have been very helpful.

            PS By the way, I got an offer to quote a data base in FileMaker Pro for am African country whole police department. If you are interested on quoting it, please send me your email so I can forward the invitation from two weeks ago. I am not that good to quote nor I know anything about FMP Server. The quote will include traveling to Africa, setting the file all over the country, training and maybe uploading information. Let me know, please.

            • 3. Re: Time calculation

              Timestamp and time fields are not the same--hence an issue to be taken into account in these calculations. I suggest that you set up the related table as it makes it easier to both manage adding more networks and also adjusting for changes in "peak" time intervals.

              left is a text function and when numbers are treated as text, "2" > "100000000" is a true statment.

              Your calculation fields that extract the left characters are probably set to return "number" as their return types and this will result in numbers being compared where the other form of your case function is comparing text.

              I'd use either the getastime or Hour functions to extract a number from the timestamp field that can be used to compare to values in the related table of network Peak time ranges.

              And I'd use a serial number field to identify each network--using a text field in the NetworkPeaks table to link to your other records so that issues with the names used don't mess up your relationships.

              • 4. Re: Time calculation

                Yes, sir. I used your suggestion and it is working like a charm without so much complication as I got into before. Thank you.