8 Replies Latest reply on Feb 14, 2017 1:23 PM by Heidi Trimble

    Is there a way to have a running total stop?

    Chris Rasmussen

      Hi -

       

      I have a graph that shows a survey response rate across weeks by group (called a 'wave') of respondents.  (Each wave was started on a different date, and we want to compare each wave against the other waves, so I have a calculated field to determine the week number for each particular group).

       

      The response rates grow (hopefully :-)) as the length of the data collection continues.

       

      The problem is that if a certain group hasn't reached the 3rd week in the data collection, their response rate just flattens out instead of just stopping.  It's misleading b/c it makes it look like they're not getting any better, rather than they just haven't gotten that far into the data collection.

       

      Are there any possible solutions?

       

      thx!

        • 1. Re: Is there a way to have a running total stop?
          David Spezia

          Hey Chris,

           

          I think I can be of some assistance.

           

          Issue:

          Tableau graphs all data points present in a data set on a graph.  You must have records for the future weeks of the waves that have not completed yet in your data set with a response rate that causes the response rate data to flatten out.

           

          Solution:

          I would attempt to solve this issue by using the filter shelf.  I would filter out (exclude) the weeks that have a flat or 0% response rate.

          If you simply cannot use the filter for 0% response rate filter, add in a quick table calc that shows percent change week over week and exlude the 0% percent change records for the table calc.

          Hope this helps!

           

          -Spezia

          • 2. Re: Is there a way to have a running total stop?
            Chris Rasmussen

            Hi Spezia -

             

            The reason I have data in the remaining weeks is because I am using the running total.  So, if there are no new responses, there is still a total for the other weeks.

             

            The Quick Table option doesn't show up for me for that data, possibly because it's a calculated field...?

             

            thanks!

            • 3. Re: Is there a way to have a running total stop?
              David Spezia

              Hey Chris,

               

              A little more complicated than I anticipated.  In this case we need to add another table calculation that uses the first table calculation.  We need to use the RunningTotal() in conjunction with a Percent_From().  To do this first Ctrl+Drop the Running Sum into the Measure Values Shelf.  Then using the pill menu for our newly created Table Calc select 'Edit Table Calc...' and Set the Calc Type to 'Percentage_From' the Percentage Along to 'Table Down' and Varies From to 'Previous' now you should have a measure showing your % Change of the Running Sum by Wave.  If it is not by wave be sure to change the Compute Using to Wave.

               

              A bit confusing, but I hope this helps.

               

              -Spezia

              • 4. Re: Is there a way to have a running total stop?
                Heidi Trimble

                I am also having this problem, though I use "problem" loosely as Tableau is behaving as its programmed to, I just need a way to work around it. There is a measure we will be monitoring this year. Progress against this measure is made in the form of additional completed items each month. The goal is to complete x items by 12/31/2017 - as a way to visualize the progress of this data concurrent to the due date I have a full time line of 2017 and a mark for the number of completed items each month. I also have a horizontal reference line showing how many, total, completed records are required by the end of the timeline. - so to briefly recap, I am using the timeline to create a vertical reference line to due date and a horizontal line for required total completions.

                The monthly totals will never visually stack up to the horizontal goal line unless I use a running total calculation, but using a running total calculation carries out the total to my due date and creates the erroneous appearance that we plateaued off and fell short of our goal.

                 

                I realize that this can be communicated via "don't trust numbers past last month" - but it's confusing and isn't the power of Tableau to create beautiful visualizations that can be quickly consumed and understood?

                • 5. Re: Is there a way to have a running total stop?
                  Heidi Trimble

                  By the way, I'm planning on resolving my issue using an analytic function in custom SQL to generate a value of running total only on the months we have completed so far. - Is this something you can do to workaround it Chris Rasmussen ?

                  • 6. Re: Is there a way to have a running total stop?
                    Andrew Watson

                    This is possible - the challenge is you're trying to filter something out that doesn't exist. Therefore you have to make it exist and this can be done via table calculations.

                     

                    In your case, and I might have the base field named incorrectly, a formula such as WINDOW_SUM(SUM([percent_completeOfMailed]))

                     

                    Set up the compute using to be Cell (one of the very few times you would use that with a window sum). Drag it to filters, making sure it is calculating per cell, then filter Special and select Non null values.

                    • 7. Re: Is there a way to have a running total stop?
                      Andrew Watson

                      Wow, just realised how old this thread is....

                      • 8. Re: Is there a way to have a running total stop?
                        Heidi Trimble

                        Oh wow you are right, way old thread! LOL, I just came across it yesterday and thought "hey! that's exactly my problem".

                         


                        I did as you suggested except I didn't add it to the filter because I want to see all the months, even the null ones. So far it looks good, thanks for the tip!