Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
sfaiter_cry
Contributor II
Contributor II

A complex formula for calculating

     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.


  

       

0 Replies