Jump to content

How can I average data in 12 hours range for specific time points

Aura Perez

Recommended Posts

I have collected glucose measurements every minute for about 24 days, and I need to average the data by 12 hour period. The periods go from 7 PM of day one to 6:59 AM of day 2 (dark cycle) and from 7 AM of day 2 to 6:59 PM of day 2 and so on and so forth. I also have data at the beginning and at the end, where neither of those periods is complete, meaning collection began outside the ranges but the range was not completed.

So far I have been able using: Avg (column 2) in the y axis and for the x axis the expression BinByDateTime([Column 1],"Year.Month.DayOfMonth.Hour.Minute",4), to display the data by minute, by hour, day, etc by moving the slider, but I have not been sussceful in grouping it by the specified 12 h period.

I also tried, deleting for now, the beginning and end of the data, so all that left ranges by 12h; and used in the y axis the expression for moving average: Sum([Column 2])

THEN Avg([Value]) OVER (LastPeriods(720,[Axis.X]))

THEN If(Count() OVER (LastPeriods(720,[Axis.X]))=720,[Value],null)

Since there are 720 minutes in 12 hours. For this iteration, I kept the same expression in the x axis: BinByDateTime([Column 1],"Year.Month.DayOfMonth.Hour.Minute",4), and after accepting the changes I only get data displayed, when I have the x slider in the min range, but nothing when i moved to the hour range or any other range. I have uploaded a small sample of the data so you can see how the data is entered and my manipulations. In this file, I am only showing one full range for each period and some data at the begining and the end of the range so the divison by 720 doe snot quite align with what I want but this give you an idea of how data is entered.

I will appreciate any help. I have not been able to find an answer for this specific question.

Link to comment
Share on other sites

Please check the attached file. I'm not sure if this is what you are looking for.

I created a calculated column that bins the date time column by 12h intervals.

I use the DateDiff function to achieve this. This needs a reference date. And as reference date I took the smallest date in your column minus one day at 7AM. See the details in the calculated column.

Another calculated columns calculates the average for each bin.

In the top visualization I use an additional data limiting expression to only display one (the first) value for each bin.

I hope this is helpful.

Link to comment
Share on other sites

Binning your time stamps to 12h intervals starting at 7AM ([DTTM_BIN]):


BinByEvenDistance(DateDiff('hour',[Column 1],
DatePart('year',DateAdd('day',-1,Min([Column 1]))),
DatePart('month',DateAdd('day',-1,Min([Column 1]))),
DatePart('day',DateAdd('day',-1,Min([Column 1]))),


Calculating the average for every 12h bin:


Avg([Column 2]) OVER ([DTTM_BIN])


Data limiting expresssion for upper visualization:


Rank([Column 1],"ties.method=first",[DTTM_BIN])=1


Use further calculated columns if you want to remove or hide uncomplete bins



Link to comment
Share on other sites

Somehow my latest comment did not get post it. Your solution is what I was looking for, now I only need to figure out how to color with just two colors: black for all points from 7PM to 7AM next day (dark cycle) and white for 7AM to 7PM in same day (light cycle)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...