4 Replies Latest reply: May 18, 2018 5:18 AM by youssef belloum RSS

    Calculate measure between two dates (Consumption)

    Daniel Sørensen

      Hello

       

      I can´t seem to find an answer to this question, so hoping someone out there is able to help me with it.

      I am working for a utility and billing company and am using Qlik for consumption and monitoring of Heat, Water, Electricity etc.

       

      Because of this, I am interested in - Consumption.

       

      So if I have this type of data.

       

      timestampenergy in Wh
      15-05-2018 00:0036940130
      15-05-2018 01:0036940170
      15-05-2018 02:0036940210
      15-05-2018 03:0036940250
      15-05-2018 04:0036940290

       

      I would like two things.

       

      1: How do I calculate the difference between two timestamps. (To use in a chart)

      Timestamp 15-05-2018 01:00 (36940170 Wh)
      
      
      Minus
      
      
      Timestamp 15-05-2018 00:00 (36940130 Wh)
      
      
      =  40 Wh
      
      
      

       

      2: To make the same calculation in the scrip.

      latest timestamp ("Electricity(Wh)") - the timestamp before this  ("Electricity(Wh)") AS  Consumption

      Valuereport:
      LOAD
          @2 as meter_no,
          @3 as timestamp,
          ,
          @5 as "Electricity(Wh)"
      FROM [lib://consumptionreports/*]
      (txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines);