Month to Month Trend Over Time to Show Months without Trend

lashansen

I have an escalation sheet and we're trying to get a report to show how many escalations over time. I was able to bucket them to show by month, but want it to show months that = 0 so it can show a true trend over the year. As you can see in my current report months with no escalations do now show up. (i.e. May, June)

Is this possible?

Eric Law

https://community.smartsheet.com/discussion/114393/month-to-month-trend-over-time-to-show-months-without-trend

Yes, you will just need a data point for that. How are you doing your roll up? Is it just a pivot that does it for you? You could most likely use a roll up sheet that aggregates this for you with the month being what the info is grabbing.

lashansen

How I have my current report being exported:

• Columns: Date of Escalation and Month #
• Filter: Date of Escalation 1/1/23 - 12/31/23
• Group: Month # - ascending
• Summary: Month # - count

What data point would I need to add to see those without escalations in them?

Paul Newcome

You will need to use a separate metrics sheet with formulas containing cross sheet references instead of a report. Then you would ad 0.00001 to the end of each COUNTIFS so that there are no values that actually equal zero, use the button on the top toolbar to hide the decimals, and then finally chart from this table.

=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023)) + 0.00001