Finding values between defined time intervals

[acme]

hi all,

i faced with a problem about calculating the differences for 15 min time intervals. I have cumulative energy data and irregular timestamps. What I would like to have is, dividing timestamps into quarters, and then calculate the total energy consumption by differences the max timestamps of the defined intervals and min timestamps of the same intervals.

For example, for the 30.09.2021 15:15 timebucket, minimum Timestamp is 30.9.2021 15:03 for this interval (timebucket), maximum Timestamp is 30.9.2021 15:14 for same time interval, and  difference should be 200-54 = 146.

And the difference should be written in another table with the related timebucket.

I got confused while searching it. 

Thanks in advance!

TimeStamp	Energy	Row	Time Bucket	COMMENT
30.9.2021 15:03:43	54	1	30.9.2021 15:15	Minimum Timestamp of the related TimeBucket
30.9.2021 15:05:14	96	2	30.9.2021 15:15
30.9.2021 15:09:19	135	3	30.9.2021 15:15
30.9.2021 15:14:34	200	4	30.9.2021 15:15	Maximum Timestamp of the related TimeBucket
30.9.2021 15:19:24	259	5	30.9.2021 15:30	Minimum Timestamp of the related TimeBucket
30.9.2021 15:24:31	280	6	30.9.2021 15:30
30.9.2021 15:29:47	343	7	30.9.2021 15:30	Maximum Timestamp of the related TimeBucket
30.9.2021 15:34:16	371	8	30.9.2021 15:45	Minimum Timestamp of the related TimeBucket
30.9.2021 15:39:19	406	9	30.9.2021 15:45
30.9.2021 15:43:38	432	10	30.9.2021 15:45	Maximum Timestamp of the related TimeBucket
30.9.2021 15:47:54	484	11	30.9.2021 16:00	Minimum Timestamp of the related TimeBucket
30.9.2021 15:52:31	525	12	30.9.2021 16:00
30.9.2021 15:58:49	620	13	30.9.2021 16:00	Maximum Timestamp of the related TimeBucket
30.09.2021 16:01:50	668	14	30.9.2021 16:15	Minimum Timestamp of the related TimeBucket
30.09.2021 16:08:56	710	15	30.9.2021 16:15
30.09.2021 16:11:30	745	16	30.9.2021 16:15	Maximum Timestamp of the related TimeBucket
30.09.2021 16:17:11	784	17	30.9.2021 16:30	Minimum Timestamp of the related TimeBucket
30.09.2021 16:20:09	825	18	30.9.2021 16:30
30.09.2021 16:20:54	859	19	30.9.2021 16:30
30.09.2021 16:28:50	884	20	30.9.2021 16:30	Maximum Timestamp of the related TimeBucket
30.09.2021 16:38:32	949	21	30.9.2021 16:45	Minimum Timestamp of the related TimeBucket
30.09.2021 16:41:40	1018	22	30.9.2021 16:45
30.09.2021 16:44:45	1030	23	30.9.2021 16:45	Maximum Timestamp of the related TimeBucket
30.09.2021 16:49:13	1082	24	30.9.2021 17:00	Minimum Timestamp of the related TimeBucket
30.09.2021 16:52:07	1140	25	30.9.2021 17:00
30.09.2021 16:55:10	1205	26	30.9.2021 17:00
30.09.2021 16:58:32	1279	27	30.9.2021 17:00	Maximum Timestamp of the related TimeBucket
30.09.2021 17:05:34	1325	28	30.9.2021 17:15	Minimum Timestamp of the related TimeBucket
30.09.2021 17:11:20	1363	29	30.9.2021 17:15
30.09.2021 17:19:28	1397	30	30.9.2021 17:30	Maximum Timestamp of the related TimeBucket

My script:

Load*Inline [
TimeStamp, Energy, Row
30.9.2021 15:03:43, 54, 1
30.9.2021 15:05:14, 96, 2
30.9.2021 15:09:19, 135, 3
30.9.2021 15:14:34, 200, 4
30.9.2021 15:19:24, 259, 5
30.9.2021 15:24:31, 280, 6
30.9.2021 15:29:47, 343, 7
30.9.2021 15:34:16, 371, 8
30.9.2021 15:39:19, 406, 9
30.9.2021 15:43:38, 432, 10
30.9.2021 15:47:54, 484, 11
30.9.2021 15:52:31, 525, 12
30.9.2021 15:58:49, 620, 13
30.09.2021 16:01:50, 668, 14
30.09.2021 16:08:56, 710, 15
30.09.2021 16:11:30, 745, 16
30.09.2021 16:17:11, 784, 17
30.09.2021 16:20:09, 825, 18
30.09.2021 16:20:54, 859, 19
30.09.2021 16:28:50, 884, 20
30.09.2021 16:38:32, 949, 21
30.09.2021 16:41:40, 1018, 22
30.09.2021 16:44:45, 1030, 23
30.09.2021 16:49:13, 1082, 24
30.09.2021 16:52:07, 1140, 25
30.09.2021 16:55:10, 1205, 26
30.09.2021 16:58:32, 1279, 27
30.09.2021 17:05:34, 1325, 28
30.09.2021 17:11:20, 1363, 29
30.09.2021 17:19:28, 1397, 30];

LOAD
[TimeStamp],
Date(Ceil([TimeStamp], .25/24),'DD.MM.YYYY hh:mm:ss') as TimeBucket,
[Energy],
[Row];

[abhijitnalekar]

Hi @acme ,

I have tried to resolve your issue, please check below.

create new dimension in the script as : time(Date(Ceil(date#([TimeStamp],'DD.MM.YYYY hh:mm:ss'), .25/24),'DD.MM.YYYY hh:mm:ss'),'mm') as bucket,

and create expression in the table as aggr(max(Energy),timen)-aggr(min(Energy),timen).

Hope this help you.

[acme]

thanks for your reply @abhijitnalekar ,

I tried your solution, as I suppose "timen" in the below chart should be my new dimension "bucket". however it didn't work, it shows the total difference without showing the timebuckets. I need to keep these differences to another table in the script with timebuckets, to match them with production numbers of same intervals.

"and create expression in the table as aggr(max(Energy),timen)-aggr(min(Energy),timen)."

[acme]

Anyone whoever deal with such kind of challange?

[abhijitnalekar]

Hi @acme ,

Please check the attached QVF file. 

I hope this helps you.

[acme]

@abhijitnalekar thanks for your reply again. I have corporate online version, so I can't open it. Is it possible to share the script?

[abhijitnalekar]

LOAD
[TimeStamp],
time(Date(Ceil(date#([TimeStamp],'DD.MM.YYYY hh:mm:ss'), .25/24),'DD.MM.YYYY hh:mm:ss'),'mm') as bucket,
Date(Ceil(date#([TimeStamp],'DD.MM.YYYY hh:mm:ss'), .25/24),'DD.MM.YYYY hh:mm:ss') as TimeBucket,
[Energy],
[Row];
Load
*Inline [
TimeStamp, Energy, Row
30.9.2021 15:03:43, 54, 1
30.9.2021 15:05:14, 96, 2
30.9.2021 15:09:19, 135, 3
30.9.2021 15:14:34, 200, 4
30.9.2021 15:19:24, 259, 5
30.9.2021 15:24:31, 280, 6
30.9.2021 15:29:47, 343, 7
30.9.2021 15:34:16, 371, 8
30.9.2021 15:39:19, 406, 9
30.9.2021 15:43:38, 432, 10
30.9.2021 15:47:54, 484, 11
30.9.2021 15:52:31, 525, 12
30.9.2021 15:58:49, 620, 13
30.09.2021 16:01:50, 668, 14
30.09.2021 16:08:56, 710, 15
30.09.2021 16:11:30, 745, 16
30.09.2021 16:17:11, 784, 17
30.09.2021 16:20:09, 825, 18
30.09.2021 16:20:54, 859, 19
30.09.2021 16:28:50, 884, 20
30.09.2021 16:38:32, 949, 21
30.09.2021 16:41:40, 1018, 22
30.09.2021 16:44:45, 1030, 23
30.09.2021 16:49:13, 1082, 24
30.09.2021 16:52:07, 1140, 25
30.09.2021 16:55:10, 1205, 26
30.09.2021 16:58:32, 1279, 27
30.09.2021 17:05:34, 1325, 28
30.09.2021 17:11:20, 1363, 29
30.09.2021 17:19:28, 1397, 30];

Please find below screens