Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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.
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)."
Anyone whoever deal with such kind of challange?
Hi @acme ,
Please check the attached QVF file.
I hope this helps you.
@abhijitnalekar thanks for your reply again. I have corporate online version, so I can't open it. Is it possible to share the script?
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