Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sfaiter_cry
New 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.


  

       

Community Browser