Hello everyone,
I stumble across a problem which had me wonder if it's at all possible in Tableau.
Question:
I have a list of jobs, I am gonna consider only one say (Job1), which runs 5 times a day described in the column below.
I am looking for a logic or solutions that substract:
Job1 Yesterday's 1st run(Completed hour) - Job1 Today's 1st run(Completed hour)
>> 6 - 5
>>1
This should apply for all other runs as well. That is, (25th 1st run - 26th 1st run), (25th 2nd run - 26th 2nd run) and so on.
The runs occurs every day, throughout the year. However I only need to consider Yesterday and todays records.
Looking forward to any suggestion or ideas. Thanks you in advance.
PS: I am unable to share the actual data, however its pretty much the same.
OK, I screen scraped the screenshot to do this. No guarantees here because we're not working with what you have. There's no way to do this without multiple table calculations, meaning nested table calculations. They're problematic for the reasons following the method posted below.
1) The data needs a 'row number' schema. Which you don't have. Which is also why this should probably be done in Tableau Prep.
Row #
INDEX()
This is set a very particular way by right-clicking the calculation (after placing onto ROWS, making it Discrete) and the selecting Edit Table Calculation. The next calculation which does the work, also needs to be set the exact same way (screenshot to follow).
Calculation 1
IF [Row #]=[Row #] THEN
DATEDIFF('hour',LOOKUP(ATTR([Completed]),-5),LOOKUP(ATTR([Completed]),0)) END-24
The dialog for the Edit Table Calculation (nested, so they both need to be set the same):
Which when all brought into the view will give you:
Now for the caveats with Table Calculations: LOOKUP() is a table calculation, so its usage is limiting. 1) table calculations provide a displayed value, not an actual value to work with going forward, such as incorporating the result in another calculation (2) attempts to remove or modify the structure of the table could cause the calculation to error or the table to collapse. Such as when creating a bar/line chart or aggregating/summarizing. Those are the caveats, the watch-outs, when using table calculations.
Which is why this effort would be better if completed in Tableau Prep, which would provide a 'real' value to work with, not a 'displayed' value.
Best, Don
(Please, don't forget to click Select as Best or Upvote !)
