AnsweredAssumed Answered

Time and Chart Conversion

Question asked by stevestearns on Mar 27, 2012
Latest reply on Mar 29, 2012 by Malcolm



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).




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,