Infotable Aggregate data for specific timeframe as hourly or daily

[PaoloDR]

Hello there,

I have a time series like this:

This is the result of a QueryPropertyHistory call for Test property of a thing.

Is there any way to group by a specifying time frame such like hourly or daily and ask to have the AVG value of "Test" column ?

I saw that in the Aggregate function you can specify the column for which aggregate data, but you can't make any operation on the data contained in that column, such like truncate the datetime value to have an hourly or daily based aggregation.

Thanks,

Paolo

[PaiChung · Accepted solution]

You are correct, I would first add the grouping column you need then you can aggregate and add that grouping with the aggregate function.

[PaoloDR]

So the only way I have to achieve the result is to literate all the InfoTable adding the column and the expected value for each row?

[PaiChung]

I would run a DeriveFields to add the column(s) you need for grouping and then run the Aggregate

[PaoloDR]

using DeriveFields, which expression should I use to achieve  this kind of result:

- hourly aggregation

from  01/01/2021 15:27:35

to 01/01/2021 15

- daily aggregation

from  01/01/2021 15:27:35

to 01/01/2021

considering that the new field  will be the date/time field that will be used in the timeseries graph on mash up

[PaiChung]

based on your date time I would do a derivefields that puts in one column the day and in one column the hour (if you are applying this on a month basis) else I would add another column with the month.

This might help a little

https://community.ptc.com/t5/IoT-Tech-Tips/Powerful-Things-you-can-do-with-DeriveFields/m-p/535264

[slangley]

Hi @PaoloDR.

If you feel your question has been answered, please mark the appropriate response as the Accepted Solution for the benefit of others in the community.

Regards.

--Sharon