cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you know you can set a signature that will be added to all your posts? Set it here! X

Infotable Aggregate data for specific timeframe as hourly or daily

PaoloDR
4-Participant

Infotable Aggregate data for specific timeframe as hourly or daily

Hello there,

I have a time series like this:

PD_9592550_0-1611591861070.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaiChung
22-Sapphire I
(To:PaoloDR)

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

View solution in original post

6 REPLIES 6
PaiChung
22-Sapphire I
(To:PaoloDR)

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
4-Participant
(To:PaiChung)

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
22-Sapphire I
(To:PaoloDR)

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

PaoloDR
4-Participant
(To:PaiChung)

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
22-Sapphire I
(To:PaoloDR)

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
23-Emerald II
(To:PaoloDR)

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

Top Tags