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

[Aura Perez]

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.

[Fabian Duerr]

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.

[Aura Perez]

Thanks for the help. I can not open your file because my version of TIBCO at CWRU is 7.11

[Fabian Duerr]

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

 

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

 

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

 

 

[Aura Perez]

Thank you. I will work on this today and see how it goes

[Fabian Duerr]

Success

[Aura Perez]

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)

[Fabian Duerr]

Please have a look here:

 

https://community.spotfire.com/questions/how-can-we-create-costum-time-bins-half-day-or-12-hours-bin