Good day!
Friends, please help solve a difficult task.
There is evidence in the fact table:
PARAM_TIME PARAM_ID PARAM_VALUE |
01.01.2015 23:00:00 957 50,94 |
02.01.2015 0:00:00 957 51,00 |
02.01.2015 0:01:00 957 51,07 |
02.01.2015 0:02:00 957 51,12 |
02.01.2015 0:03:00 957 51,14 |
02.01.2015 0:04:00 957 51,17 |
02.01.2015 0:05:00 957 51,20 |
02.01.2015 0:06:00 957 51,24 |
02.01.2015 0:07:00 957 51,29 |
02.01.2015 0:08:00 957 51,30 |
02.01.2015 0:09:00 957 51,27 |
02.01.2015 0:10:00 957 50,66 |
02.01.2015 0:11:00 957 49,80 |
02.01.2015 0:12:00 957 49,17 |
02.01.2015 0:13:00 957 48,69 |
02.01.2015 0:14:00 957 48,33 |
02.01.2015 0:15:00 957 48,05 |
02.01.2015 0:16:00 957 47,84 |
02.01.2015 0:17:00 957 48,46 |
02.01.2015 0:18:00 957 49,27 |
02.01.2015 0:19:00 957 49,78 |
02.01.2015 0:20:00 957 50,16 |
02.01.2015 0:21:00 957 50,42 |
02.01.2015 0:22:00 957 50,63 |
02.01.2015 0:23:00 957 50,79 |
01.01.2015 23:00:00 954 6548,32 |
02.01.2015 0:00:00 954 6550,46 |
02.01.2015 0:01:00 954 6552,6 |
02.01.2015 0:02:00 954 6554,74 |
02.01.2015 0:03:00 954 6556,88 |
02.01.2015 0:04:00 954 6559,01 |
02.01.2015 0:05:00 954 6561,15 |
02.01.2015 0:06:00 954 6563,29 |
02.01.2015 0:07:00 954 6565,43 |
02.01.2015 0:08:00 954 6567,57 |
02.01.2015 0:09:00 954 6569,71 |
02.01.2015 0:10:00 954 6571,85 |
02.01.2015 0:11:00 954 6573,98 |
02.01.2015 0:12:00 954 6576,12 |
02.01.2015 0:13:00 954 6578,26 |
02.01.2015 0:14:00 954 6580,4 |
02.01.2015 0:15:00 954 6582,54 |
02.01.2015 0:16:00 954 6584,67 |
02.01.2015 0:17:00 954 6586,81 |
02.01.2015 0:18:00 954 6588,95 |
02.01.2015 0:19:00 954 6591,09 |
02.01.2015 0:20:00 954 6593,23 |
02.01.2015 0:21:00 954 6595,37 |
02.01.2015 0:22:00 954 6597,51 |
02.01.2015 0:23:00 954 6599,65 |
Directory:
CIPHER PARAM_ID
T 957
M 954
These data must be represented in table form
Data | T | M |
02.01.2015 | | |
Where the daily value is calculated according to the formula Mday = (M0h - M23pr) + (M1h - M0h) ... + (M23h - M22h) where M23pr - the value of the volume of the previous day from 23:00.
The daily temperature is calculated by the formula Tday = (T0h * (M0h - M23pr) + T1h * (M1h - M0h) ... + T23h * (M23h - M22h)) / ((M0h - M23pr) + (M1h - M0h) + ... + (M23h - M22h)), where M23pr - the value of the volume of the previous day from 23:00.
How to calculate the formula in Expression?
My calculation stop there, but this is not true, can someone be able to develop the idea:
Num(
(
Sum({<PARAM_ID={957,954}>}aggr(Sum({<PARAM_ID={957}>}PARAM_VALUE)*(Sum({<PARAM_ID={954}>}PARAM_VALUE)
-Sum({<PARAM_ID={954},PARAM_TIME={$(#=Only(PARAM_TIME)-1)}>}PARAM_VALUE)),PARAM_DATE))
)/Sum({<PARAM_ID={957,954}>}aggr(Sum({<PARAM_ID={954}>}PARAM_VALUE)
-Sum({<PARAM_ID={954},PARAM_TIME={$(#=Only(PARAM_TIME)-1)}>}PARAM_VALUE),PARAM_DATE))
,'# ##0,00')
There is PARAM_DATE Date(PARAM_ТIМЕ,'DD.ММ.YYYY')
Thanks for the help.
