Burndown chart

TMonty0319
TMonty0319 Member
in Charting

I am trying to create a card that shows a burndown representation of my data. My data has to do with Courses, estimated time to Complete (Duration), and Actual Hours to complete. I think to create a burndown I need a new column that gives total Duration per year of all courses due that year and then I want to be able to subtract hours to complete for each month and show burndown over the year. Anyone have any thoughts on best way to do this?

Course

Year Due

Month Due

Duration

Hours to Complete

A

2023

December

1

0.5

B

2024

February

1

1

C

2024

March

1

1.5

D

2025

January

2

1

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can add your group names to the partition list

    PARTITION BY `Year`, `Other Group`
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    You can get the total for each year with a window function:

    SUM(SUM(`Duration`)) OVER (PARTITION BY `Year`)

    You can get a running total with another window function:

    SUM(SUM(`Hours to Complete`)) OVER (PARTITION BY `Year` ORDER BY `Month`)

    NOTE: Your Month needs to be an actual date or a numerical representation to be sorted properly; otherwise, it'll sort alphabetically and be incorrect.

    Combining these two, you get your burn down:

    SUM(SUM(`Duration`)) OVER (PARTITION BY `Year`)
-
SUM(SUM(`Hours to Complete`)) OVER (PARTITION BY `Year` ORDER BY `Month`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • TMonty0319
    TMonty0319 Member

    Thanks that is working. How can i modify formula if i want to have different lines for different groups? I tried putting the groups in series on chart but didnt work well and the lines for each group were not connected and is just a bunch of data points

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can add your group names to the partition list

    PARTITION BY `Year`, `Other Group`
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • TMonty0319
    TMonty0319 Member

    That worked. Thanks!

Categories