Forum Discussion

DamGils's avatar
DamGils
Regular Visitor
8 years ago
Solved

Linear interpolation with differents counters

Hello world,

 

It has been two days since I have a problem to handle my database, I really need help. My problem concerns the linear interpolation between two values, I'll explain :

 

My database is like this,

 

DateCountidValue       
15/05/2017                 46            425  
15/05/2017                 47                   14  
15/05/2017                 48                 356  
18/05/2017                 46            600  
18/05/2017                 47                   26  
18/05/2017                 48                 563  

 

 

As you can see, I have several meters at several times. Here I want to specify that the number of meters can be variable, just like the number of date. To estimate the consumption, I need to linearized between two statements. Thanks to the difference between two days, I so wish to obtain the consumption, like that :

 

DateCountIdValueConsumption
15/05/2017                 46                 425   
16/05/2017                 46                 483                      58  
17/05/2017                 46                 542                      58  
18/05/2017                 46                 600                      58  
15/05/2017                 47                   14   
16/05/2017                 47                   18                         4  
17/05/2017                 47                   22                         4  
18/05/2017                 47                   26                         4  
15/05/2017                 48                 356   
16/05/2017                 48                 425                      69  
17/05/2017                 48                 494                      69  
18/05/2017                 48                 563                      69  

 

Didi anybody think he can help me ?

 

Thank you in advance for your help and do not hesitate to ask me questions if I was not rather precise,

 

Have fun :) 

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    DamGils- I'm not understanding the linear interpolation aspect of this. Are you trying to calculate the Consumption column? If not that, what is the expected output?

    • DamGils's avatar
      DamGils
      Regular Visitor
      8 years ago

      HI Greg_Deckler - Thanks a lot for your answer ! I'll re-explain to you. So, my database is like this :

       

      DateCountidValue
      15/05/2017                 46                 425  
      15/05/2017                 47                   14  
      15/05/2017                 48                 356  
      18/05/2017                 46                 600  
      18/05/2017                 47                   26  
      18/05/2017                 48                 563  

       

      I try to linearized the values between two dates for each count, like this for CountId 46 for example:

       

      DateCountIdValue
      15/05/2017                 46                       425  
      16/05/2017                 46   x3
      17/05/2017                 46   x4
      18/05/2017                 46                       600  

       

      Concretely I look for both line in red and I want to calculate x3 and x4. They can be calculate like this :

       

      x3=425+((600-425)/(DATEDIFF(18/05/2017;15/07/2017)

       

      As you understood, the final result fo which I try to obtain is the consumption. The latter is reckoned by making the valuable difference between two dates, as this :

       

      DateCountIdValueConsumption
      15/05/2017                 46                 425   
      16/05/2017                 46                 483                      z1
      17/05/2017                 46                 542                      z2
      18/05/2017                 46                 600                      z3 

       

      z1 = 483 - 425 = 58

       

      Finally, I want to reply this operation for each counters automatically, and obtain this :

       

      DateCountIdValueConsumption
      15/05/2017                 46                 425   
      16/05/2017                 46                 483                      58  
      17/05/2017                 46                 542                      58  
      18/05/2017                 46                 600                      58  
      15/05/2017                 47                   14   
      16/05/2017                 47                   18                         4  
      17/05/2017                 47                   22                         4  
      18/05/2017                 47                   26                         4  
      15/05/2017                 48                 356   
      16/05/2017                 48                 425                      69  
      17/05/2017                 48                 494                      69  
      18/05/2017                 48                 563                      69  

       

      (all reds values are calculated)

       

      I hope that the explanation of my problem is more clear this time,

       

      Thank you in advance for your answers :)

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        Yes, much more clear, I appreciate it. And I appreciate you providing the data in a format that I can copy and paste. I should have some time to work on this today but this is going to be a bit involved to work through.