Burndown chart

TMonty0319

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

GrantSmith

You can add your group names to the partition list

PARTITION BY `Year`, `Other Group`

GrantSmith

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`)

TMonty0319

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

You can add your group names to the partition list

PARTITION BY `Year`, `Other Group`

TMonty0319

That worked. Thanks!