how to chart a metrics sheet

OshaK

Hello, thanks to @Neil Watson I was able to create a metrics sheet that calculates number of cases per month and number of patient complications by month. I need to be able to create a chart from it, but when i go to dashboard and try to create a chart, it tells me the data is not chartable. How can i make a graph out of it. Please help!

Ryan Sides

What kind of chart do you want? What data do you want displayed? Can you draw it out ?

You can chat the data you have here, but you'll need to have numbers in there, not just zeros. The chart will not build with all zeros.

OshaK

@Ryan Sides thank you, i'm trying to do a bar chart similar to attached. I only have two cases this year, so they are not all 0s. I'm attaching last year with more cases, but some complications still all 0. so it's not possible to just exclude them in a chart? I will have more data as year progresses.

also for some reason, one complication gives me =UNPARSEABLE even though i use the exact same formula. What am i doing wrong?

Neil Watson

@OshaK when you create your chart, there is an option to select the columns you wish to include

OshaK

@Neil Watson It feels a bit buggy because i get a different behavior - sometimes I click on Add data and get an error, and sometimes in the Columns drop-down there is only one Option. But I think I was able to do it. Thank you so much! Is it possible to create a single chart for several years (we have data 2018-)

I need to combine the data for every months for all the years.

Neil Watson

@OshaK since you have a helper column with the year, you can expand the COUNTIFS with additional cross-sheet criteria:

=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1), {Year}, "2023")

You will need a separate table in this sheet for each year, or a separate sheet (depending on how you want to manage your data)

OshaK

@Neil Watson , thank you, I tried to create a separate sheet but not sure - is this how it needs to be set up? it gives me an INVALID REF. Sorry, I don't think I understand.

Or it should be like this? that didnt' work either. Thank you!

Neil Watson

@OshaK once you have your first sheet set up and working (say with 2023 data) then File, Save as New and create a new sheet. Then edit the formula for 2022 etc.

OshaK

@Neil Watson thank you, Neil, I'm still getting INVALID REF error and for the sake of this world, can't figure out why. I'm referencing the correct sheet.

Am I missing something in this metrics sheet?

=COUNTIFS({Month}, @cell = $[Numerical Value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1), {Year}, "2023")

Neil Watson

@OshaK might be easier to jump on a video call and discuss, let me know the best way to get hold of you

OshaK

@Neil Watson thank you so much! I think i figured the formulae, so it's all good. But now somebody asked me why I'm doing it by year and didn't combine all years into one sheet which can be defined into dynamic views. I tried to combine the metrics sheets, but now can't seem to make a chart from. How can i create a single line chart for Total cases by month per Month/Year from the child rows that have parent rows?

also is it possible to calculate Total cases per each year and Total cases for ALL years in this single sheet and also add to the Dashboard as Metrics? Thank you!

OshaK

@Neil Watson If I try to highlight the rows like this, I can't make the line chart, it makes no sense. Thank you again!

Neil Watson

@OshaK if you want 2022 data, then you need to select the data related to 2022 only (see below).

OshaK

I need data for all years together on a single chat without the parent rows (2021 and 2022) @Neil Watson thank you!

Neil Watson

@OshaK do you want the data to be added together (2012 + 2022) or do you want 2 years data displayed?

OshaK

I want it like this but I dont' want the parent row (2022 and 2021) to be displayed. If i don't select them while Add Data, SS tells me the data needs to be in the adjacent cells and can't be charted. thank you @Neil Watson