I have a column chart to show the values of commission sales per month. To do this i used the date utilites pipe to get the month name from the date field (sale date) and sorted on this, but it sorts the chart alphabetically.
I looked to see if i could get the month number from the date field but that is not an option in the date utilities pipe.
Any ideas on how i could achieve this please?
You can do this using an equation field in the data table:
CONCAT(DATE_FORMAT({Date}, “%m”)," - ",MONTHNAME({Date}))
This will format the date to this: 12 - December
appreciate this @SuiteUpstairs and good to know about this feature.
Having a small problem using this. If i choose the equation output as a number, the field is available to choose Group By in the chart setting but orders incorrectly.
If i change the output to Text the field is not available to Group By in the chart settings. I did find a way round it.
seems to still work and orders correctly.
Yes, I forgot to mention that last night. I’m glad you were able to figure it out.
It doesnt let me use the following : (DATE_FORMAT({Date}, “%m”)
Any ideas why or how to work around ?
Hey @SalBan
Is your date field titled “Date”?
If not, please make sure you are using your field names in the examples, between the {} symbols.
CONCAT(DATE_FORMAT({your_field_name}, “%m”)," - ",MONTHNAME({your_field_name}))
Change your Output Type to “Text” as the output of this equation contains a number + text.
We already tried that and still doesn’t work.
It let us validate: CONCAT(MONTHNAME({Date}))
and CONCAT(WEEKOFYEAR({Date}))
BUT NOT: (DATE_FORMAT({Date}, “%m”)
Here’s something else you can try…
Remove the quotes and type them back in. Sometimes copying from the community doesn’t paste correctly.
