5 Replies Latest reply on Aug 21, 2013 2:29 PM by Pedro Machado

    My table calculation (running_sum) does not work if I use a continuous variable to color the chart.

    Anne Jenkins

      I am trying to show a growing backlog and the age of the open service orders.  I used a custom SQL statement to create a joined data table with event date = created or completed.  The I made a calculated field and used a running_sum to show the count of service orders still open at the end of a given period ("active count").  

       

      I'm displaying the counts by month over the 18 months since we started tracking in a new database.  I'm using an orange-to-blue gradient to show age.  On the attached dashboard, the chart on the left uses MY(Created Date) to color the counts, and the chart on the right uses Created Date (exact date).  MY is discrete, so I created the gradient manually and would need to shift the color assignment as time goes by.  But when I use Created Date, or any other continuous variable, the running_sum doesn't work.  It includes in the active count for each month only those records with values of the coloring variable that match the records created that month.

       

      The 3rd worksheet was an attempt to use another age measure (total business days open, calcuated outside Tableau).  If I treat the variable as continuous, I have the same problem as with Created Date -- it includes in the active count for any month only those records whose age in days is the same as one of the ages that occurs for records created that month.  (If I change the measure to discrete, it has an unmanageable number of values to hand-color.)

       

      My sense of the problem is that I want the running_sum calculation to happen across the months before the coloring variable is applied, but I don't know how to do that.  I'd appreciate any insights.