7 Replies Latest reply on Jul 13, 2012 1:16 PM by Jonathan Drummey

    showing last results

    Maciej Zebrowski

      Hi,


      I have data source with test results. There are 3 columns:

      -     date

      -     result

      -     test_id

       

      I'd like to show each week number of  "Passed", "Failed" and "Blocked" tests (stacked bar chart).

       

      I created calulation field "my_week" for each week to be used on Columns shelf.

      datename('week',date)+"'"+right(datename('year',date),2)

       

      I am struggling with showing last result per test_id in each week. 2 rules apply here:

      1) if last week test was Blocked and this week is "Passed", then this week number of Blocked should decrease by 1, and number of "Passed" should increase by 1

      2) last result for each test within each week is valid

       

      I attached sample text file with data.

       

      Any idea ? Looking foward to your recomendations !

        • 1. Re: showing last results
          Jonathan Drummey

          Hi Maciej,

           

          Here's an attempt at it. What I did was created a calculated field called Max Date Flag of the form:

           

          LAST()==0

           

          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,

           

          Jonathan

          • 2. Re: showing last results
            Maciej Zebrowski

            Hi Janathan,

             

            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.

             

            Thanks !

            • 3. Re: showing last results
              Jonathan Drummey

              Hi,

               

              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.

              • 4. Re: showing last results
                Maciej Zebrowski

                Hi

                 

                I attached packaged workbook - and look at this thread you mentioned.

                 

                Thanks,

                • 5. Re: showing last results
                  Jonathan Drummey

                  Hello Maciej,

                   

                  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?

                   

                  Jonathan

                  • 6. Re: showing last results
                    Maciej Zebrowski

                    Hi Jonathan,

                     

                    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

                     

                    Maciej

                    • 7. Re: showing last results
                      Jonathan Drummey

                      Hi,

                       

                      Did you ever get this figured out?