-
1. Re: Calculate time difference between 2 sequential records
Jonathan DrummeyFeb 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 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
-
Townsville Solar.twbx 1.1 MB
-
-
3. Re: Calculate time difference between 2 sequential records
Jonathan DrummeyFeb 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
-
Townsville Solar jtd.twbx 1.1 MB
-
-
4. Re: Calculate time difference between 2 sequential records
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.
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 DrummeyFeb 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 Feb 23, 2015 9:02 AM (in response to Jonathan Drummey)Thanks again Jonathan.
Yes - I get Calculation_####### (a long series of digits).
-
PastedGraphic-3.tiff 24.9 KB
-
-
7. Re: Calculate time difference between 2 sequential records
Jonathan DrummeyFeb 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 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
-
PastedGraphic-3.tiff 24.9 KB
-
-
9. Re: Calculate time difference between 2 sequential records
Jonathan DrummeyFeb 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 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.
-
PastedGraphic-3.tiff 24.9 KB
-