3 Replies Latest reply on Jul 4, 2016 4:28 PM by Shinichiro Murakami

    Getting the difference of two running totals

    David Throop

      My data is attached.  (A toy version of the real data set.)

       

      I'm tracking tickets in an action-tracking system.  Each record has a

      • Project-ID
      • Date
      • Status
      • Old Status

      Status is no_value, Submitted, Assigned, Analyzed, Authorized, Corrected, Closed or Rejected

      For example: The first line says that on 04/02/13, Ticket A_4 went from a status of no_value to Submitted.

       

      I want to have a graph that shows, week by week, how many tickets are in each status. Here's what I did so far.  I

      • Put  sum([Number of Records]) on the Rows shelf,
      • Set it to a Quick Table Calculation of Running total
      • Put Week(Time) on the Columns shelf
      • Put Status onto Color

      That gives me a family of curves - the cumulative of every ticket that's ever been in the state Summited, Assigned...

      Then, on another sheet, I do the same thing except I put Old Status onto color.  That gives me another family of curves - every ticket that's ever ceased to be in the state Submitted, Assigned...

       

      What I want to (I think) do is take the difference between those two families.  But I can't figure out how to write the calculation expression.  Can you help?

       

      As a side question, I note that the family of Status curves stops after the last instance of a particular status.  There's just one record Rejected, and it shows a single dot.  What I'd like to do is have the curve continue flat to the right side of the chart.  How do I?

       

      Thanks

      David

       

      FWIW: I'm in Tableau Desktop 9.3

        • 1. Re: Getting the difference of two running totals
          David Throop

          I should have submitted a packaged workbook; not my Excel source.  Here tis

          • 2. Re: Getting the difference of two running totals
            Shinichiro Murakami

            I'm not sure I understand your request correctly, anyways..

             

            Because the status is changed with old and new, you can not use "Status" for both old/new.

            I mean old data has old status and new data has new status and these need to be counted with their own status.

             

            To achieve this, at first you should re-structure your data with data-pivot as below.

             

            Delta is calculated as following.

            [delta]

            zn(countd(if [Pivot field names]="PR/ER Status" then [Pr/Er Id] end))

            -

            zn(countd(if [Pivot field names]="History.Old State" then [Pr/Er Id] end))

            You can put sum(Number of record) as primary Axis with Color of new/old.

            And Running_sum of [delta] to secondary axis.

             

            Thanks,

            Shin

            • 3. Re: Getting the difference of two running totals
              Shinichiro Murakami

              Still not sure which is the VIZ to show what you want to show.

              One idea have is like below.

               

              Old status as fatter/lighter color line and New status as thinner/darker color line.

              And add two bar charts to show what is changed.

              Talking about flat line chart, you can not accomplish that with " Continious " line.

              It's only available with " Discrete " date axis and below formula.

               

              [ZN count]

              running_sum(zn(sum([Number of Records])))

              Then created dual axis chart to show the difference overall count.

               

               

              And to how individual status change, created bar graph.

              I added "Negative" to how "Old status" which reduced number of item from respective status, and add positive with New Status which increased number of item to status.

               

              [Negative]

              -sum([Number of Records])

               

               

              Thanks,

              Shin