14 Replies Latest reply on Nov 6, 2015 9:21 AM by Karen Massey

    how to create a burn down chart?

    Karen Massey

      Is it possible to use tableau calculations to create a burn down chart?  I have reviews scheduled throughout the quarter (scheduled date) and would like to see how many are left to be conducted at any point within the quarter (conducted date = null).  For example, the data file contains dates in the Scheduled Date and Conducted Date columns:

      Burndown data.PNG

      I want to be able to create a burndown chart that counts the total number of programs, then shows number conducted per week along with a calculation of number left to do:

       

      Burndown.PNG

      and then show it in a viz:

      burndown viz.PNG

       

      I'm struggling with how to create the counts per week in the quarter.  It seems that I could count occurrence of "not null" in the conducted date, but how to specify the weekly date range?

       

      Any help or links are greatly appreciated!

        • 1. Re: how to create a burn down chart?
          Karen Massey

          Sample data attached

          • 2. Re: how to create a burn down chart?
            ann.stolzman

            Hi Karen,

            Take a look at this thread (and it's correct answer) and see if this helps.  It's based on a chart, not a crosstab, but I think the logic should apply Burndown Chart - Design Target Line

             

            -Ann-

            • 3. Re: how to create a burn down chart?
              Karen Massey

              The formula in the post draws a 45 degree line across a chart.  What I am hoping to find is a formula or set of formulas that will look for values in my date field and count occurrences in specific date ranges.  That would allow me to count number of reviews that I need to do,  number done in each week, and number left to do at any point in time.

               

              1.  Identify starting date and ending date (e.g., weeks 1 - 12), and total number of reviews needed.  I can do this by Schedule Date shown by weeks and count of programs, e.g., number of records.

               

              2.  Identify a date range from starting date through week end (Friday) of week 1 through week 12.  Is there a formula that sets a date to "now" by week?

               

              3.  Count occurrences of "conducted date" in the date range for week 1.  Something like, "if date range = week 1, 1, else 0"?

               

              4.  Iterate - count occurrences of "conducted date" in each subsequent date range through the ending date.  Totally lost on this.  Would I repeat creation of a calculated field in #3, 12 times for each week in the quarter?  Or is there a way to iterate?

               

              5.  Plot occurrences of conducted date from the starting point (total) through each week to the end date. 

               

              And just to make it fun, I am on Tableau Desktop with no real ability to use SQL so was hoping to do this via calculated fields.

               

              Hopefully,

              Karen

              • 4. Re: how to create a burn down chart?
                Cole Gruberth

                Hi Karen,

                 

                Here's what I tried.

                First I calculated "Total Scheduled Count" as

                     { FIXED : COUNT([Scheduled Date])}

                The level-of-detail FIXED : function without specification means it will always sum over all the data points in your source, regardless of the subdivisions you're displaying.

                 

                Then for the "To-do" calculation, I have

                     max([Total Scheduled Count])- RUNNING_SUM([Count Conducted])

                 

                If WEEK(Scheduled Date) is in the Columns shelf, and To-do is on the Rows shelf, I think that gets you to where you want to go.

                 

                One warning-- I'm pretty sure the FIXED syntax is going to operate regardless of any filters you might apply, so if your raw data extends past the quarter that you want to display, I'm not sure you'd get the right value for Total Scheduled Count, even if you filtered just to the quarter.

                Maybe there's a way to calculate a value of the quarter, and use some syntax like {FIXED [Quarter] : Count.... }

                Or ideally that could be a parameter?

                Guess that's another day's exploration.

                 

                Best,

                   Cole

                • 5. Re: how to create a burn down chart?
                  kettan

                  If I understand your question right, then attached workbook contains data for desired outcome.

                  It would be easier to help if its raw data before transformation also was available.

                  Just a small anonymous data sample is enough.

                  The smaller the simpler and thus actually the better.

                  • 6. Re: how to create a burn down chart?
                    kettan

                    And just to make it fun, I am on Tableau Desktop with no real ability to use SQL so was hoping to do this via calculated fields.

                    Summarized by weeks (12 weeks = 12 rows).  No SQL.  Only calculations.  This won't be easy/possible 

                     

                    As for SQL, it might be available on Desktop. It depends on your data source and Operative System.

                    If your data is in Excel (or Text file), your O/S is Windows, then SQL*Jet is available via the Legacy Connector.

                     

                    You could probably also generate a summarized data table in Excel with some lookup formula 

                    • 7. Re: how to create a burn down chart?
                      Karen Massey

                      When I create the To Do calculation, I get an error, "all fields must be aggregate or constant when using table calculation functions.  Any ideas?  The twbx file in the original post has the data tab.

                      • 8. Re: how to create a burn down chart?
                        Karen Massey

                        Hi - the attached workbook contains the raw data in the tab called "Data". And unfortunately, my comment on having no real ability to use SQL was rooted in my lack of knowledge of SQL. 

                         

                        It seems like a running sum of a count (like in Cole's post) would be possible, but the aggregation error is and issue.  Any ideas would be appreciated!

                        • 9. Re: how to create a burn down chart?
                          kettan

                          the attached workbook contains the raw data in the tab called "Data"

                          Found it   The reason I thought it wasn't was because it wasn't seen in Data pane above Dimensions.

                           

                          It seems like a running sum of a count (like in Cole's post) would be possible, but the aggregation error is and issue.  Any ideas would be appreciated!

                          I don't have more time to help today. I think a running sum will get you closer.

                          Workbook with running sum is attached which hopefully attracts other helpers to contribute.

                          thread 192932 how to create a burn down chart.png

                           

                          Attached Workbook Version:  9.0

                          .

                          • 10. Re: how to create a burn down chart?
                            Cole Gruberth

                            Hi again, Karen

                             

                            Looks like I left out a step!

                            I was working only from the "Data" sheet as Data Source.

                             

                            I had created a calculated field called "Count Conducted" --

                            COUNT([Conducted Date])

                             

                            That should have been step 1.

                             

                            I'm guessing that you created a count of your variable "Conducted" from the "Burndown" sheet (very logical, given I didn't say otherwise), and something about working across the two tables is causing the error.

                             

                            Hope that goes more smoothly!

                                  -  Cole

                            1 of 1 people found this helpful
                            • 11. Re: how to create a burn down chart?
                              Cole Gruberth

                              Nice.

                              I think your "Running" calculation gets Karen where she needs to go in one step, and it should adjust to whatever range she shows in the viz.

                                 - Cole

                              • 12. Re: how to create a burn down chart?
                                Cole Gruberth

                                Except... I was thinking Karen wanted the total to be based on the number of scheduled reviews, as opposed to a count of records.

                                 

                                If that's what you want, Karen, then using kettan's approach, you could calculate "Running" as

                                TOTAL(COUNT([Scheduled Date])) - RUNNING_SUM(COUNT([Conducted Date]))

                                 

                                Cheers,

                                    -Cole

                                • 13. Re: how to create a burn down chart?
                                  kettan

                                  Thanks, appreciated 

                                  • 14. Re: how to create a burn down chart?
                                    Karen Massey

                                    Hi - Thanks for your suggestions!  I ended up using Kettan's formula for counts this way:

                                     

                                    TOTAL(SUM([Number of Records])) - RUNNING_SUM(SUM(IF NOT ISNULL([Conducted Date])

                                    THEN 1 ELSE 0 END))

                                     

                                    It may be that the COUNT and RUNNING_SUM combination caused the error "all fields must be aggregate or constant when using table calculation functions"