10 Replies Latest reply on Mar 29, 2012 12:03 AM by Malcolm

    Time and Chart Conversion

    stevestearns

      Hi,

       

      As a bit of background... I suffer from critically severe complex sleep apnea and have been working with a medical team for the past 3-years to determine the continued source of my daytime drowsiness and fatigue (and lack of focus and thus my question). Since my team of doctors, say that my sleep apnea is now well treating, we are trying to understand why I still have daytime drowsiness and fatigue, therefore my team of doctors have asked me to track a variety of daily metrics to see a pattern over time that will hopefully lead to answers and a solution.

       

      Since Apple Numbers is a great charting tool, I started the solution in Numbers, however with the volume of data, I think FileMaker Pro 11 would be a better, more flexible and robust tool longer term and I am working on converting it over. I will either use FileMaker Pro/ Go or Numbers as the presentation layer.

       

      The FileMaker Pro database solution has three tables related to sleep data. I have provided a subset of the records from each table for your reference.

       

      The Sleep table contains:

       

      Bedtime, BedtimeChart, BedtimeChartAdj, HoursSlept, SleepDate, SleepDuration, SleepNotes, SleepQuality, SunriseChart, SunriseTime, SunsetChart, SunsetChartAdj, SunsetTime, WakeTime, WakeTimeChart

      "2/27/2012 6:38 PM","17.37","17.37","10","2/27/2012","10h","","6","5.6","6:24 AM","12.88","18.48","5:31 PM","2/28/2012 4:38 AM","7.37"

      "2/28/2012 10:07 PM","13.88","13.88","5.8","2/28/2012","5h 48m","","6","5.63","6:22 AM","12.85","18.48","5:31 PM","2/29/2012 3:55 AM","8.08"

      "2/29/2012 8:06 PM","15.9","15.9","6.3","2/29/2012","6h 18m","","7","5.67","6:20 AM","12.78","18.45","5:33 PM","3/1/2012 2:24 AM","9.6"

      "3/1/2012 8:11 PM","15.82","15.82","10.1","3/1/2012","10h 6m","","7","5.68","6:19 AM","12.74","18.42","5:35 PM","3/2/2012 6:17 AM","5.72"

      "3/2/2012 10:15 PM","13.75","13.75","7.2","3/2/2012","7h 12m","","7","5.72","6:17 AM","12.68","18.4","5:36 PM","3/3/2012 5:27 AM","6.55"

      "3/3/2012 8:24 PM","15.6","15.6","7.2","3/3/2012","7h 12m","","7","5.73","6:16 AM","12.65","18.38","5:37 PM","3/4/2012 3:36 AM","8.4"

      "3/4/2012 10:00 PM","14","14","7.4","3/4/2012","7h 24m","","7","5.77","6:14 AM","12.6","18.37","5:38 PM","3/5/2012 5:24 AM","6.6"

       

      Data is entered into: HoursSlept (is a number field and is entered as decimal hours 8.1) and WakeTime (is a timestamp field and is entered as 2/28/2012 4:38 AM). I have a calculation to determine the Bedtime (timestamp) and SleepDuration (calculation to convert HoursSlept 8.1 to 8h 6m). SleepDate, SleepNotes, and SleepQuality are manually entered.

       

      Sunrise and Sunset for the Boston area are lookups from the SolarLunarTimes table and contains:

       

      Date, Sunrise, Sunset, Duration, Difference, SolarNoon, Altitude, Distance

      "2/27/2012","6:24 AM","5:31 PM","11h 7m 19s","+ 2m 48s","11:57 AM","39.3¡","148.147"

      "2/28/2012","6:22 AM","5:32 PM","11h 10m 8s","+ 2m 48s","11:57 AM","39.7¡","148.182"

      "2/29/2012","6:20 AM","5:33 PM","11h 12m 57s","+ 2m 49s","11:57 AM","40.0¡","148.217"

      "3/1/2012","6:19 AM","5:35 PM","11h 15m 46s","+ 2m 49s","11:56 AM","40.4¡","148.252"

      "3/2/2012","6:17 AM","5:36 PM","11h 18m 36s","+ 2m 49s","11:56 AM","40.8¡","148.288"

      "3/3/2012","6:16 AM","5:37 PM","11h 21m 27s","+ 2m 50s","11:56 AM","41.2¡","148.324"

      "3/4/2012","6:14 AM","5:38 PM","11h 24m 17s","+ 2m 50s","11:56 AM","41.6¡","148.361"

       

      Chart Adjustments are lookups from the ChartAxis table and contains:

       

      MilitaryTime, ClockTime, ChartXAxisValue

      "13","1:00 PM","23"

      "14","2:00 PM","22"

      "15","3:00 PM","21"

      "16","4:00 PM","20"

      "17","5:00 PM","19"

      "18","6:00 PM","18"

      "19","7:00 PM","17"

      "20","8:00 PM","16"

      "21","9:00 PM","15"

      "22","10:00 PM","14"

      "23","11:00 PM","13"

      "0","12:00 AM","12"

      "1","1:00 AM","11"

      "2","2:00 AM","10"

      "3","3:00 AM","9"

      "4","4:00 AM","8"

      "5","5:00 AM","7"

      "6","6:00 AM","6"

      "7","7:00 AM","5"

      "8","8:00 AM","4"

      "9","9:00 AM","3"

      "10","10:00 AM","2"

      "11","11:00 AM","1"

      "12","12:00 PM","0"

       

      I am looking for some "guidance" on how to most efficiently calculate the chart adjustment values for the chart data for two charts that will be overlaid over each other in either FileMaker Pro or Numbers (as a presentation layer). Each of the calculations adjusts the data to the scale of 0 (zero) to 23, with 12:00 PM being 0 (zero) and 1:00 PM (23), based up the ChartAxis table using the ChartXAxis values. This assumes a person sleep at night, rather than a day sleeper.

       

      The first chart is a stacked bar chart with Sunrise and Sunset data to shade the area that it is night (and is set in the background). The bottom segment of the stacked bar chart is from 12:00 PM to Sunrise (which is made transparent to make it invisible). Sunrise to Sunset is second stacked bar chart to shade night hours.

       

      The second chart is a line chart that illustrates Bedtime and WakeTime and the background is set with an opacity of 0 (zero) to be able to see the first background chart (see chart below).

       

      sleep.png

       

      The calculations from Numbers are:

       

      SunsetChartAdj =LOOKUP(HOUR('2/10' $'Sunset (Time)'),$Hour,$Chart X Axis)-ROUND(MINUTE('2/10' $'Sunset (Time)')/60,2)

       

      BedtimeChartAdj =LOOKUP(HOUR('2/10' $'Bedtime (Time)'),$Hour,$Chart X Axis)-ROUND(MINUTE('2/10' $'Bedtime (Time)')/60,2)

       

      Sunrise ='2/10' Sunrise

       

      Sunset ='2/10' 'Sunset (Adjusted Chart Time)'-'2/10' Sunrise

       

      Bedtime ='2/10' Bedtime

       

      WakeTime =LOOKUP(HOUR('2/10' $'Wake Time (Time)'),$Hour,$Chart X Axis)-ROUND(MINUTE('2/10' $'Wake Time (Time)')/60,2)

       

      What is the most efficient and flexible manner to recreate these calculations in FileMaker Pro for use in FileMaker Pro or FileMaker Go? Is it by using a lookup, a lookup calculation, would the mod calculation help, or is there some other approach?

       

       

      Thanks for your guidance,

      --

      Steve

        • 1. Re: Time and Chart Conversion
          stevestearns

          Here is what I have been able to do so far. I was able to reduce the number of calculation fields from what I had in the original Numbers spreadsheet. It is not the most flexible because it is a calculation rather than a table. Hopefully, someone else will have some good suggestions or approaches.

           

          ChartValueSunrise (Calculation (Number)) =

          Case ( not IsEmpty ( WakeTime ) and

          Hour (SunriseTime) = 13 ; 23 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 14 ; 22 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 15 ; 21 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 16 ; 20 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 17 ; 19 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 18 ; 18 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 19 ; 17 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 20 ; 16 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 21 ; 15 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 22 ; 14 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 23 ; 13 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 0 ; 12 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 1 ; 11 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 2 ; 10 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 3 ; 9 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 4 ; 8 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 5 ; 7 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 6 ; 6 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 7 ; 5 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 8 ; 4 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 9 ; 3 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 10 ; 2 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 11 ; 1 - Round ( Minute (SunriseTime)/60; 2) ;

          Hour (SunriseTime) = 12 ; 0 - Round ( Minute (SunriseTime)/60; 2) ; 12 )

           

          ChartValueSunset (Calculation (Number)) =

          Case ( not IsEmpty ( WakeTime ) and

          Hour (SunsetTime) = 13 ; 23 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 14 ; 22 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 15 ; 21 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 16 ; 20 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 17 ; 19 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 18 ; 18 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 19 ; 17 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 20 ; 16 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 21 ; 15 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 22 ; 14 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 23 ; 13 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 0 ; 12 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 1 ; 11 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 2 ; 10 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 3 ; 9 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 4 ; 8 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 5 ; 7 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 6 ; 6 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 7 ; 5 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 8 ; 4 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 9 ; 3 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 10 ; 2 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 11 ; 1 - Round ( Minute (SunsetTime)/60; 2) ;

          Hour (SunsetTime) = 12 ; 0 - Round ( Minute (SunsetTime)/60; 2) ; 12 ) - ChartValueSunrise

           

          ChartValueBedtime (Calculation (Number)) =

          Case ( not IsEmpty ( WakeTime ) and

          Hour (Bedtime) = 13 ; 23 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 14 ; 22 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 15 ; 21 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 16 ; 20 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 17 ; 19 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 18 ; 18 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 19 ; 17 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 20 ; 16 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 21 ; 15 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 22 ; 14 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 23 ; 13 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 0 ; 12 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 1 ; 11 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 2 ; 10 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 3 ; 9 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 4 ; 8 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 5 ; 7 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 6 ; 6 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 7 ; 5 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 8 ; 4 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 9 ; 3 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 10 ; 2 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 11 ; 1 - Round ( Minute (Bedtime)/60; 2) ;

          Hour (Bedtime) = 12 ; 0 - Round ( Minute (Bedtime)/60; 2) ; 12 )

           

          ChartValueWakeTime (Calculation (Number)) =

          Case ( not IsEmpty ( WakeTime ) and

          Hour (WakeTime) = 13 ; 23 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 14 ; 22 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 15 ; 21 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 16 ; 20 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 17 ; 19 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 18 ; 18 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 19 ; 17 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 20 ; 16 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 21 ; 15 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 22 ; 14 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 23 ; 13 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 0 ; 12 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 1 ; 11 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 2 ; 10 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 3 ; 9 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 4 ; 8 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 5 ; 7 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 6 ; 6 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 7 ; 5 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 8 ; 4 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 9 ; 3 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 10 ; 2 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 11 ; 1 - Round ( Minute (WakeTime)/60; 2) ;

          Hour (WakeTime) = 12 ; 0 - Round ( Minute (WakeTime)/60; 2) ; 12 )

          • 2. Re: Time and Chart Conversion
            comment

            I am having a hard time understanding the issue. Could you clarify what is the data that you are collecting (input), and what do you want your chart to present (output) - leaving the things that you have done aside for the moment?

            • 3. Re: Time and Chart Conversion
              RubenVanDenBoogaard

              Hi Steve,

               

              I have been playing with your data and turned it into a chart.

              Unfortunately the standard FM charting does not give you the floting barcharts, but the calculations are way easier then what you use.

               

              Hope this helps,

               

              Best regards,

               

              Ruben van den Boogaard

              Infomatics Software

              ruben@infomatics.nl

              1 of 1 people found this helpful
              • 4. Re: Time and Chart Conversion
                stevestearns

                Hi Michael,

                 

                Thanks for your response.

                 

                I am collecting for input, the HoursSlept (number) and WakeTime (timestamp). I am looking to have a chart that shows the adjusted Sunset, Sunrise, Bedtime, and WakeTime. The goals is to have either a FileMaker Pro/ Go chart or the data to generate a chart in Numbers to illustrate the data as represented in the image in my original post. I say "adjusted" data in the Sunset, Sunrise, Bedtime, and WakeTime, because the data has to be translated to a scale (as defined in the ChartAxis table).

                 

                Maybe there is a more efficient approach to achieve the same objective.

                 

                Any thought to approaches would be appreciated.

                 

                Thank you,

                --

                Steve

                • 5. Re: Time and Chart Conversion
                  stevestearns

                  Hi Ruben,

                   

                  Wow! Thank you for all your efforts in providing not only a solution, but an actual FileMaker Pro database to be able to review and study.

                   

                  I like your streamlined approach to the calculations. These are much easier than the Case statements I wrote.

                   

                  Thank you,

                  --

                  Steve

                  • 6. Re: Time and Chart Conversion
                    comment

                    stevestearns wrote:

                     

                    The goals is to have either a FileMaker Pro/ Go chart or the data to generate a chart in Numbers to illustrate the data as represented in the image in my original post. I say "adjusted" data in the Sunset, Sunrise, Bedtime, and WakeTime, because the data has to be translated to a scale (as defined in the ChartAxis table).

                     

                    Perhaps it can be even simpler:

                    • 7. Re: Time and Chart Conversion
                      Malcolm

                      Michael, your post was censored by Jive. Please post again.

                      • 8. Re: Time and Chart Conversion
                        stevestearns

                        Hi Michael,

                         

                        Your solution is much simpler and more streamlined. I like that it can be done right within FileMaker Pro without needing to move it to another app to graph it.

                         

                        On 2/26/2012, I did not sleep, which caused some strange issues with the graph and a date, I updated the calculation to be:

                         

                        cStartTS = If ( Hours = 0 ; EndTS - 86400 ; EndTS - 3600 * Hours)

                         

                        Also, I realized that for the Sunset and Sunrise data, you need to have one more day beyond the graph (i.e., 3/28/2012) otherwise the Sunrise field is null and graph goes up to 12 (12:00).

                         

                        I am uploading the modified SleepLog.fp7 database you so graciously provided to me.

                         

                        Thank you so much for all your assistance,

                        --

                        Steve

                        • 9. Re: Time and Chart Conversion
                          comment

                          Malcolm wrote:

                           

                          Michael, your post was censored by Jive. Please post again.

                           

                          No, it wasn't. There is an attachment following the colon

                          • 10. Re: Time and Chart Conversion
                            Malcolm

                            Michael, your post was censored by Jive. Please post again.

                             

                             

                            No, it wasn't. There is an attachment following the colon

                             

                            Ah. Out here in email land we don't see that. I think a feature request is in order. It would be nice to have a link to the attachment supplied via email.

                             

                            Malcolm