Metric by Month

Hello!

Appreciate any formula help as always!

Currently, we have several people fill out a form and on that form is an "Escalation date" which is just the day they are submitting the request.

For example, in May, we had 20 escalations submitted and the actual sheet has those 20 rows with the escalation date column being 05/01, 05/05, 05/05, 05/07, etc etc.

I want to be able to put on the dashboard a graph or metric showing that 20 escalations were input in May and then use the formula to do the same for January - September (and then future months). Any thoughts on what I could do?

Thank you!

Find more posts tagged with

Accepted answers

All comments

Monique Odom Stearn

Hello @WesMcNally,

My suggestion would be to create a buddy column to calculate the month for that entry (or month and year) and then you can either create a sum formula for metrics or a chart using the data.

Amber Eakin

You can also do this without a helper column if you would like. You would use the COUNTIF formula with the MONTH function. It would look something like the formula below, where 5 is the number representing May.

=COUNTIF([Escalation date]:[Escalation date], MONTH(@cell) = "5")

Please let me know if that works for you!

Edited to add: You could do these formulas for each month in a separate metric sheet or in the Sheet Summary. You could do a chart based on the metric sheet or a Sheet Summary report, or you could use the Sheet Summary field as a metric (if you don't want a chart).

WesMcNally

Hey @Amber Eakin . Thank you for the info. I can't seem to get it to work as I would like. I keep getting Invalid data type error. I referenced the sheet and highlighted the escalation date column. Anything I am doing wrong?

Amber Eakin

@WesMcNally - Is that "Escalation Date" column an actual "Date" column type? If it's Text/Number, you'll get that "#INVALID DATA TYPE" error message.

WesMcNally

@Amber Eakin Yes, it's an actual Date column type.

Amber Eakin

Oh good! Without actually seeing the reference, I'm not sure. The formula works for me, so the only thing I can figure is the reference.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.