Dashboard

Bryan Zheng · 04/12/21

Hello everyone,

I am trying to make my first Dashboard. On my data sheet, I have dates from Jan 1 2021-March 31 20201. Instead of showing all individual days, How can I make it only show month like January, February, and March to summarize data monthly?

Thanks,

Bryan

Paul Newcome · 04/12/21

Does the {Date} range contain actual date values in a date type column?

If not, try correcting that first. If so or if you still continue to get that particular error, try this little tweak...

=COUNTIFS({Date}, IFERROR(MONTH(@cell), 0) = MonthNo@row, {Indicator}, 1)

The IFERROR will account for blanks and non-date values within the date range so that the COUNTIFS can function properly.

Heather Duff · 04/12/21

Hi Bryan,

What I generally do is set up a separate sheet with formulas (with cross-sheet references) to summarize the data I want to display. To go further into detail, you may want to provide screenshots of your source data (with any sensitive material blacked out), and describe what kind of widgets you are using (metrics, chart, report, etc.).

Thanks,

Heather

Bryan Zheng · 04/12/21

https://community.smartsheet.com/discussion/comment/287061#Comment_287061

Hi Heather,

Thank you so much for answering my question. I want to use Chart to display how many redflags that each month has. I was trying to do it on a different sheet to summarize the data, but I am struggling with the formula.

Thanks for your help

Bryan

Heather Duff · 04/12/21

@Bryan Zheng ,

I see one thing that may cause you problems with your source sheet - your primary column is the one you're using for dates. In order for formulas to work with dates, you will want to assign a different column as your primary column. You could even create a new column, leave it blank, set it as the primary column, and hide it. Then you'll want to right click on the current primary column, go into column properties, and set it as a Date column.

Here's how I would set the summary sheet up:

Month - type in one month for each row (January-December)

MonthNo - type in 1 through 12 (in the January row, type 1, July is 7, December is 12, etc.)

Count - use this formula: =countifs({date},@cell=MONTH(MonthNo@row),{Indicator},1)

-- When you're setting up the formula and you get to =countifs( you'll want to then click the Reference Another Sheet link, look up the source sheet, and select the column you would like to use. When you do this, it will come up with an automatically-generated name. It's best practice to name your range in the box above where the columns are listed (see screenshot below). You'll have to repeat it again for the Indicator column.

Named ranges show up in {brackets}, so I've named your source sheet column that is currently called "Primary Column" {date}, and "Cold Blue Indicator" range is named {indicator}.

From there, when you set up your chart widget, you'll be able to select the summary sheet, and select all three columns of data. You can then go into the chart options and edit which columns you want to include in your chart, so you can take out the MonthNo column.

Hope this helps! Let me know if you have any questions.

Best,

Heather

Bryan Zheng · 04/12/21

https://community.smartsheet.com/discussion/comment/287066#Comment_287066

HI Heather,

When I type in the Countifs function and I get #invalid data type. I did change the primary column to blank and added another column for date. here is my summary sheet. what did I do wrong?

Thank you.

Bryan