Not sure if this will make sense. I have an excel table with a range defined as qryFCC_Data1. In this table, there is a field called TransDateTime. This field contains hourly readings for different sensor tags, so data for April 18th shows up as 4/18/2018 0:00, 4/18/2018 1:00, 4/18/2018 2:00 and so on.
The table itself contains 8 days of data...so 192 hourly records.
I am trying to write a dynamic formula that will average the trailing 24 hours of data. What I have is shown below.
=AVERAGEIFS((qryFCC_Data1[FCFC401.PV]),(qryFCC_Data1[TransDateTime]),MAX(qryFCC_Data1[TransDateTime]))
So this finds the column/sensor tag FCFC401.PV and then should average the data points where the values in the TransDateTime column meet the criteria of MAX TransDateTime. So if my data points are 4/11/2018 to 4/18/2018, I want to average all the values for 4/18/2018 for tag FCFC401.PV. However, since the TransDateTime has hourly references, the max value it finds is for 4/18/2018 23:00. I have tested the formula on just data only part and it works great.
So the question: Is there a way for me to format the TransDateTime table value to just Date within the actual AverageIFS formula? In other words, without adding another column of data (these are pulled from Teradata DB), can I convert 4/18/2018 0:00, 4/18/2018 1:00, 4/18/2018 2:00 to just 4/18/2018 on the fly within the formula?
I'm having troubles with it...so not sure it is possible. Thanks in advance.
The table itself contains 8 days of data...so 192 hourly records.
I am trying to write a dynamic formula that will average the trailing 24 hours of data. What I have is shown below.
=AVERAGEIFS((qryFCC_Data1[FCFC401.PV]),(qryFCC_Data1[TransDateTime]),MAX(qryFCC_Data1[TransDateTime]))
So this finds the column/sensor tag FCFC401.PV and then should average the data points where the values in the TransDateTime column meet the criteria of MAX TransDateTime. So if my data points are 4/11/2018 to 4/18/2018, I want to average all the values for 4/18/2018 for tag FCFC401.PV. However, since the TransDateTime has hourly references, the max value it finds is for 4/18/2018 23:00. I have tested the formula on just data only part and it works great.
So the question: Is there a way for me to format the TransDateTime table value to just Date within the actual AverageIFS formula? In other words, without adding another column of data (these are pulled from Teradata DB), can I convert 4/18/2018 0:00, 4/18/2018 1:00, 4/18/2018 2:00 to just 4/18/2018 on the fly within the formula?
I'm having troubles with it...so not sure it is possible. Thanks in advance.