its me again. To make it more simple, i have a screen shot, which explains in more detail, what is the basic strucutre of the table and data-fields in question (green), what calculation is done to get the time a status type was hold (blue) and the aggregation result expected (red). How to do this best in Tableau? (e.g. attached screenshot shows that a specific equipment was producing/running 16 hours!)
To find out how long a status was applied i need to perform a difference between the times-tamp of one record to the time-stamp of the consecutive record.
Have a look at the attached to see if it helps! This is kind of a process analysis, which requires calculation of time difference between 2 dates/times for every record. SQL is particularly inefficient for this type of queries, as it is not able to handle well intra-record calculations. To reach a solution I used a subquery that basically returns the next following timestamp for each record, and then the timespan of each operation is an easy subtraction (also with division to get to your numbers). Word of warning: I believe this will not scale well in a biggish data set.
Hope this helps,
347416.twbx 16.1 KB
Many thanks Vladislav. This is a good and easy solution to me.
Your remark in regard to scalability is also a very big point for me.
I have other tables, which contain information to be shaped before getting showed on Real-Time-Dashboard based.
I've got the feeling it would be best to put a ETL System, which delivers the table with the time-intervalls already calculated and let tableau just do the aggregation, right?
Absolutely! Let the ETL do the dirty calculations, as it does many more things, I believe. In it you can handle also complications like crossing day boundaries, etc much more easily than trying to do so in Tableau. And then just do your visualization in Tableau - so to speak use the right tool for the right job