3 Replies Latest reply on Dec 31, 2013 2:33 PM by Jonathan Drummey

    Difference Calculation

    dean.knickerbocker

      Hi All

       

      I have some workflow data that is organized in a simple table such as

       

      Date, Event ID, Status

       

      where Status can be one of 8 fields.  One original requirement was to display trending lines per date for various combinations of the Status field.  That was trivial to do.  Then I drilled a little deeper and found they really needed changes in status per date, which was also easy enough to do with the difference table calculation.  Now they would like to get even more granular.  They would also like to represent this difference data as the number of events that changed to this state, changed from this state and stayed the same.

       

      I have pondered this for a bit and just don't think I can do this in Tableau.  But I thought I would put it out there before I wrote some custom code to calculate the data myself.

       

      Thanks in advance.

      Cheers

      DK

        • 1. Re: Difference Calculation
          Shawn Wallwork

          Dean, I'm almost certain one of the gurus can help you can arrive at the values you describe, but you'll need to post a packaged workbook with some dummy data for them to be able to help you.

           

          --Shawn

          • 2. Re: Re: Difference Calculation
            dean.knickerbocker

            Hi Shawn

             

            Attached is a very rudimentary sample.  I used 3 states (Begin, In Process and Done).  I have used In Process as the example.

             

            Essentially on sheet 2 I am looking to go one level deeper that the difference calculations and depict the actual numbers that combine for the difference.  In this example my Week 2 difference is Plus 3 and that is the results (3 Changes to this State, 1 Change From this State and 1 Stayed the Same).

             

            I hope this makes sense.

             

            Thanks

            Dean

            • 3. Re: Difference Calculation
              Jonathan Drummey

              Hi Dean,

               

              See the attached. The basic process is the common one used when we want to count something at one level of detail (state changes in this case) and then sum them up at a different level of detail, we use a nested table calculation. In this case, there is a table calculation that for each check of the state change returns 1 or Null, and the Compute Using for this calc is the Week (so it partitions on each ID). Then for each check there's a wrapper calc that uses something like:

               

              IF FIRST()==0 THEN

                  WINDOW_SUM([Changed To Flag])

              END

               

              The Compute Using for this calc is on ID so it sums up all the flags for the week, retaining the nested calculation that addresses on the week.

               

              I added an additional First Filter calc with the formula FIRST()==0 with a Compute Using of ID, this removes the extra data that is necessary for the computations to work but not needed for the final view.

               

              The workout worksheet shows all the calcs, you can see one set of results in the view worksheet.

               

              There were a few questions/comments I had on this:

               

              - The Sheet 2 where you had calculated a difference from prior is not counting what you think it is. Because you have a regular filter on Status "In Process", that is only what is being returned to Tableau. So the difference from prior calc is counting 2 IDs in week 1 that had In Process Status and 5 IDs in week 2 status, it's not actually tracking changes from one week to another. You can see this in the "in process" worksheet.

              - The totals you describe for Week 2 don't match the data. Looking at the Status changes for each ID, I count 4 changes to in Process, and one away from In Process, and one the same. You can see this in the "in process changes" worksheet.

              - Based on your statements it seemed like you weren't trying to track which states each ID changed from and to, but more that there were state changes. If you actually want to count the state changes for each state and what it came from or went to, that would require a different route.

              - Related to that, I didn't completely understand what you meant by "changes to this state", if you really want to be tracking "how many went from X to Y" then a different method would be necessary. In that case, one question is whether you are guaranteed to have every possible combination of state changes (all 28 of them) in the data for a given timeframe, and whether you have to show all those combinations regardless of whether or not they are in the data, or whether you just want to show totals of the state changes that exist.

               

              Jonathan