10 Replies Latest reply on Mar 2, 2015 2:28 PM by Kirby Wanner

    Calculate time difference between 2 sequential records

    Kirby Wanner
    Kirby Wanner Feb 19, 2015 3:10 PM

      Hello

       

      I have a solar pv database that includes a timestamp and the associated power value at that time.  I want to create an energy measure which in my case will be the power value x (difference in time between Timestamp and most recent previous timestamp).  I then subsequently want to create a further measure called Efficiency which is Energy/{a constant}.  Although I seem to be able to do this as a calculated field, the resulting measure cannot be readily managed.  For example, the only way it displays is if I assign my Date-Time field as "actual date-time".  As a result I cannot do time based aggregations (ie Efficiency over hour, day, week periods, 5 min bins) etc...

       

      Any  thoughts on how I can better set up to allow these calculations?  I am guessing related to my yet early and incomplete understanding of the various data cagtegories in Tableau.

       

      Thanks

      • 1026 Views
      • Tags:
        • 1. Re: Calculate time difference between 2 sequential records
          Jonathan Drummey
          Jonathan Drummey Ambassador Feb 20, 2015 4:41 AM (in response to Kirby Wanner)

          Hi Kirby,

           

          Welcome to the forums! Can you post a packaged workbook with some sample data, or at least some sample data? Also, what is your data source and how comfortable are you with SQL? (One solution would be to do some pre-processing in SQL).

           

          Jonathan

          • 2. Re: Calculate time difference between 2 sequential records
            Kirby Wanner
            Kirby Wanner Feb 21, 2015 11:47 AM (in response to Jonathan Drummey)

            Thanks Jonathan

             

            Attached is the workbook.  The sheet Energy has my energy calculation that does not allow for the various measures (avg, sum, etc..).

             

            I had considered that perhaps I need to create a view or table in Mysql that creates the time difference measure pre Tableau.  I am not proficient but can get assistance there if that is the most effective solution.  I have also been trying to use the Tableau data source option "Custom SQL".  Again, not sure if better to handle with the orginal data source or use this Tableau function.

             

            Any advice you could provide would be appreciated.

             

            Kirby

            • 3. Re: Calculate time difference between 2 sequential records
              Jonathan Drummey
              Jonathan Drummey Ambassador Feb 21, 2015 2:58 PM (in response to Kirby Wanner)

              I'm trying to understand the data and what you're trying to do with it. I have a question about the attached workbook, I set up a worksheet that shows that for each id (Meter) there are 2 or 3 records in the underlying data. When I look at the underlying data, I can see that the only difference is the Attribute_id and occasionally the Value, even though everything about the address is the same.

               

              Now, I'm (potentially incorrectly) assuming that Value is something like watts, so using SUM(Value) in the Power calc would end up incorrect making the power seem bigger than it is. If the Value is more like watt-seconds, then summing it would make sense.

               

              Secondly, I'm not understanding the point of the Power (Adj for Nulls) calc. The way it's written, assuming the addressing of the calculation is on the date dimension if there was a Null value then it would carry forward the previous value of the calc through time, which means that if you had 12pm non-Null value, 6pm Null, 12am Null, then the 6pm and 12am would both have whatever that noontime value was. Since this is for solar panels I'm guessing that's not what is desired.

               

              Finally, what is the ultimate use of the efficiency calc? Is it to have one value per Name, or something else?

               

              Jonathan

              • 4. Re: Calculate time difference between 2 sequential records
                Kirby Wanner
                Kirby Wanner Feb 22, 2015 12:41 PM (in response to Jonathan Drummey)

                Sorry Jonathan - unfair of me not to provide more context with the workbook.   Most of your assumptions are correct, but for clarity the attached image captures the data structure and hierarchy.Screen Shot 2015-02-22 at 1.32.28 PM.jpg

                With the calculated energy I want to be able to do the various measure activities over different time spans ie sum, avg, etc over hours, days, weeks, months.

                 

                I included the correction for nulls as there are circumstances where our meter does not return a value (null).  This is different from zero (which is a feasible reading).  When null, it is a meter coms issue and I am trying to estimate or interpolate what the unit was likely generating at that time by looking at its last reading.  Not perfect, but I think better than assuming 0 or that the time represented by the timestamp is invalid.

                 

                I have been playing around with using the Custom SQL function to bring the timestamp already converted to a date-time (rather than doing the conversion in Tableau).  Not sure if this helps or hurts what I am trying to do, but easy enough if required.

                 

                One other question, my Timesstamp conversion table calculation becomes unusable when I refresh my extract and I essentially have to redo the calc.  Any thoughts on why this would be or what I an do to ammend it.

                 

                Thanks again for your assistance

                 

                Kirby Wanner

                • 5. Re: Calculate time difference between 2 sequential records
                  Jonathan Drummey
                  Jonathan Drummey Ambassador Feb 23, 2015 5:43 AM (in response to Kirby Wanner)

                  Hi Kirby,

                   

                  I'll have time to look at this in detail tomorrow. In the meantime when you say the timestamp calc becomes unusable on extract refresh, can you give more details? Are you seeing Calculation_#######.... or something else?

                   

                  Jonathan

                  • 6. Re: Calculate time difference between 2 sequential records
                    Kirby Wanner
                    Kirby Wanner Feb 23, 2015 9:02 AM (in response to Jonathan Drummey)

                    Thanks again Jonathan.

                     

                    Yes - I get Calculation_####### (a long series of digits).

                     

                    • 7. Re: Calculate time difference between 2 sequential records
                      Jonathan Drummey
                      Jonathan Drummey Ambassador Feb 27, 2015 9:53 AM (in response to Kirby Wanner)

                      Hi Kirby,

                       

                      I've had another look at this and I have another question about the calculations. There can be up to 41 records in a given 5 minute interval, so the power calc that would work accurately at the raw timestamp level fails when it's SUM([Value])/100000. When using a five minute interval, should the avg Value be used, last value, first value, etc?

                       

                      Jonathan

                      • 8. Re: Calculate time difference between 2 sequential records
                        Kirby Wanner
                        Kirby Wanner Feb 27, 2015 11:24 AM (in response to Jonathan Drummey)

                        It should be average - this is where I have been stumbling is trying to create a new 5 minute dataset (1 value per 5 minute interval, never null).  If I had this as a measure that I can work with sums, average of different time periods etc… it would be perfect.

                         

                        Thanks again

                         

                        • 9. Re: Calculate time difference between 2 sequential records
                          Jonathan Drummey
                          Jonathan Drummey Ambassador Feb 27, 2015 11:30 AM (in response to Kirby Wanner)

                          What are your volumes (# of meters, # of years you're going to analyze at a

                          time)? The reason I ask is because making the tradeoff of doing some

                          computation in the data source (and potentially some padding) might be

                          faster/easier in Tableau than trying to do everything in Tableau.

                           

                          On Fri, Feb 27, 2015 at 2:24 PM, Kirby Wanner <

                          • 10. Re: Calculate time difference between 2 sequential records
                            Kirby Wanner
                            Kirby Wanner Mar 2, 2015 2:28 PM (in response to Jonathan Drummey)

                            Hi Jonathan

                             

                            Volumes are quite low at this time <100 meters and only a few months of data.  Both will grow, but slowly.