0 Replies Latest reply: Jan 15, 2016 1:09 AM by Vasiliy Kashper RSS

    A complex formula for calculating

    Vasiliy Kashper

           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.