Here's an attempt at it. What I did was created a calculated field called Max Date Flag of the form:
Put that on the Filter shelf and set the Compute Using to String of Date (Tableau can introduce excessive padding for table calculations that involve dates, so I created a field that was STR([date]) to get around that). The String of Date and test_id neeed to be on the Level of Detail, with Week of Date on Columns and SUM([Number of Records]) on Rows. The bars are colored using ATTR([result]) instead of just [result] in order to prevent Tableau from trying to partition the table calculation using the [result] field. (There are ways around that using an Advanced compute using, but I was trying to keep this simple.
I'm really not clear about the rules you listed. For your rule #1), you list a special rule to decrement Blocked. I'm confused about this. Given that only the last results for the week count (rule #2), if last week the last result was Blocked and this week was Passed then last week's Blocked shouldn't even be counted in this week's results?
Let me know if this works for you,
Thank you very much for your reply.
I attached my Tableau 6.1 workbook where I tried to do what you recommended - I can't see yours as it is created in Tableau 7, right ?
Rule #1 - I try to see running_sum of all #Blocked etc, so assuming tests 1,2 and 3 was Blocked in week 7,
and test 2 was Passed in week 8, then week 7 should show #3 of Blocked and week 8 should show #2 of Blocked and #1 of Passed. The issue is that tests 1 and 3 don't have to be run at all in week 8.
test_results.twb.zip 3.8 KB
You'll need to save the file as a .twbx (packaged workbook) so I can see your view and data in context.
Based on your additional description, I'm wondering whether you'd be wanting to use a queue. Here's a link to a post about building queues in Tableau: http://community.tableau.com/thread/107815.
I had another chance to take a look at this, can you confirm whether this is what you are looking for?
- for each status, show a running sum of the (final for the week) test results, which is really three running sums, Blocked, Passed, and Failed.
- if a test is not performed in a given week, the running sum from the prior week is carried over, as in the case for test #2 that was Blocked in week 21 and there are no results for test #2 in week 2, so there should still be a Blocked for test #2 in week 22.
One question I have, is what should appear in the intervening weeks where there are no rows in the underlying data? For example, in the sample data there is no data for weeks 18-20. Should those weeks still be displayed, even when no tests were run at all?
1) Running_sum of each status is not good, see an example below:
week result test_id
20 blocked 1
21 passed 1
running_sum of blocked in week 21 would be 1 instead of 0...because this is the same test_id
so sum of passed may have an impact on sum of the other (blocked or failed)
2) if there is no data for given week I won't display it - I heard that Tableau 7 can magically add missing data, but this not needed for me
3) I am trying to generate the following chart but no success yet - see attached workbook)
- for each test_id take last result for each week and if Passed return 1 else 0 (#passed calculation field)
- I put test_id on "Level of details" shelf
- put sum of #passed on row shelf
- created max_date (last()=0) and put to the filter shelf
- then played with Compute Using Week of Date - i assume last() function should running in each week - but to my surprise there is no bar for Week 16..
If you could help me with that and explain how this Compute Using works I'd appreciate it very much !
Thank you for your time
test_results.twbx.zip 13.0 KB
Did you ever get this figured out?