Place Months in chronological order on a Dashboard Trendline Chart Widget?

Context: I am building a trendline report to track number of projects started each month. It has two series, a 2024 and 2025 series.

Two keep the months in order, I have put a number in front of the month, e.g. "03. March" so that when I create the trendline the months are not out of order. (e.g. in a previous iteration April was before March).

Question: Is there a way for me to remove the numbers in front of the months while still maintaining the correct calendar order?

For Reference:

This is how I've sorted the data:

This is the formula I used for the months:

=IF(MONTH([Start Date]@row) = 1, "01. January", IF(MONTH([Start Date]@row) = 2, "02. February", IF(MONTH([Start Date]@row) = 3, "03. March", IF(MONTH([Start Date]@row) = 4, "04. April", IF(MONTH([Start Date]@row) = 5, "05. May", IF(MONTH([Start Date]@row) = 6, "06. June", IF(MONTH([Start Date]@row) = 7, "07. July", IF(MONTH([Start Date]@row) = 8, "08. August", IF(MONTH([Start Date]@row) = 9, "09. September", IF(MONTH([Start Date]@row) = 10, "10. October", IF(MONTH([Start Date]@row) = 11, "11. November", "12. December")))))))))))

Thanks!

Find more posts tagged with

Reports

Dashboards

Accepted answers

All comments

Philip Robbins

@RL5500 I know this doesn't get you to where you want, but the practice I always employ with months and years to avoid any issues with sorting is to plot them against a helper column in the YYYY-MM format. They remain readable and most clients I build solutions for find this format acceptable. The only watch out is to ensure that the months from Jan-Sep get a prefixed 0, rather than ending up in YYYY-M format.

I appreciate you want to overlay years across the same range of months, so this doesn't solve things for you. Off the top of my head, your method of numbering each month should be more robust than just using the month names.

RL5500

@Philip Robbins Thank you for replying! Just to confirm are you talking about adding a helper column that shifts the start date into the YYYY-MM format? And using a formula to produce that?

Philip Robbins

@RL5500 yes that's correct. I realised after posting I wasn't really anywhere near answering your question though! Do you have an additional sheet creating the chart or are you producing it directly from the data shown?

RL5500

@Philip Robbins so what I do is I have one sheet that has the dates and the formulas, then I create a report grouping and summarizing the data, and then I use the Dashboard Chart Widget to create the trendline!

Qq are we able to create charts directly in the sheet itself? So far I haven't been able to create charts except in the dashboard.

Philip Robbins

No, you cannot create charts within sheets - only in dashboards.

I thought you must have a summary somewhere as I couldn't replicate your chart using the data snapshot only!

RL5500

@Philip Robbins oh is that true? That's interesting…

So this is how I set up the data in the report. I then use the chart widget and use the report as a data source, and then turn the chart into a trendline.

I group by month so that the X-axis is month.

And then I summarize by 2024 and 2025 and those create my series.

Philip Robbins

@RL5500 that's clearer now. What would happen if you split the month number into a different column, sorted on it, but then plotted the Month and year columns?

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.