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...
Need Help
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.

  • DamGils's avatar
    DamGils
    Regular Visitor
    8 years ago

    Good, It was the purpose. I understand the complexity of my request, it's exactly for that it has been now three days whom I am on the subject. I wait for your return and thank you for your help.