Designing a chart on an interface, by month, irrelevant of year.

[EllieBailey]

Hi,

I have a field with created dates. I would like to be able to create a chart that shows this data, grouping by the month of the created date. However I want the year to be irrelevant. For example, March 2024 and March 2022 to be shown in the same group within the chart.

I have created a new field with a formula to show the month only which works perfectly. But then when I create a chart on the interface, they are in alphabetical order and not date order which is not want we want.

Can't think of a way to make the chart display the months in date order, or another way for this all to work. Any help is appreciated!

[TheTimeSavingCo · Accepted solution]

What if you used a single select instead?  And we'd populate the single select with an automation that pasted the formual value into the select field

Link to base   

[kuovonne]

Here are some options.

Have the formula return a month as a number instead of the month name.

MONTH({date field})

Have the formula return a text string, but preface the string with the two digit month number.

DATETIME_FORMAT({date field}, "MM MMM")

Have the formula return a date value for the first of the month in a fixed year.

DATETIME_PARSE(
  MONTH({date field}) & "/1/2024",
  "M/D/YYYY"
)

[EllieBailey]

I thought this too, but the issue is the month number is still ordered alphabetically rather than numerically. 

[EllieBailey]

Ooooh, I like this. I am trying to set up the automation but I am unsure how to make it do all previous records? I've got the trigger set to if the single select field is blank. Please may you help, which automation did you use?

Thanks so much!

[EllieBailey]

I've figured this all out now. Thank you so much!