5 Replies Latest reply on May 21, 2018 4:20 PM by Mitchell Stone

    Daily total consumption

    Mitchell Stone

      Hi everyone, first time poster!

       

      My query is as follows:

       

      I have a cumulative graph that shows the total litres passed through a water meter over time. I get around 3000 records a day from a single device is running 24/7 to create this graph.

       

      The next thing I need to do is show the total water used per day using this same data. Is it possible to find the difference between a record at midnight on one day to another record on midnight the previous day and then graph that, or something very similar?

       

      Any help would be greatly appreciated.

       

      Thanks for your time

        • 1. Re: Daily total consumption
          Michal Mokwinski

          Hi Mitchell,

           

          do I understand correctly that you data is cumulative on daily basis? What I mean is the value is being zeroed at midnight.

          If that's the case why not use MAX() function if the chart is meant to be showing daily water consumption?

           

          MichaƂ

          • 2. Re: Daily total consumption
            Mitchell Stone

            Hi Michal,

             

            Unfortunately it is not that easy. It is a forever accumulating value.

             

            e.g. The ever increasing amount of water that runs through a water meter

             

            Thanks for helping!

            • 3. Re: Daily total consumption
              Peter Fakan

              Hi Mitchell,

               

              Post us up some data - I'm thinking a dual axis histogram with lookup (date) -1 on one axis is what you are looking for but I can't translate that into something workable until I see some data.

               

              HTH

               

              Peter

              • 4. Re: Daily total consumption
                Eric Hammond

                Hi Mitchell,

                 

                • Drag your date field to the Columns shelf and drill down to the day level
                • Drag [Liters] to the Rows shelf, and change the aggregation to MAX() - the default was likely "SUM".
                • Right-click on the MAX([Liters]) pill in the Rows shelf, click "Quick table calculation", then "Difference".
                • In the Marks shelf, change the mark to bar.  (or, if you just want to see numbers, drag the MAX([Liters]) pill from the rows shelf and place it on the label tile on the Marks shelf).

                 

                This should give you the liters used each day as it uses a table calculation to find the difference between the previous day's highest reading and the current day's highest reading.

                • 5. Re: Daily total consumption
                  Mitchell Stone

                  Good morning Eric,

                   

                  Thank you for taking the time out of your day to help.

                   

                  This is exactly what I was after. I was pretty close to begin with, it was changing the aggregation to MAX that got me over the line.

                   

                  Again, than you for the help!