Grouping data by month and sorting it chronological way in a row report

axelrodo

Hello everyone,

I'm trying to create a report in which the data pulled in (in this case "submissions"), is grouped in months (i.e., 3 submissions in August, 5 in September, 2 in October, etc.) as to show which months are busier. The grouping and the sorting tools though, in their options "sort ascending" or "sort descending", do an ALPHABETIC sorting in which August is followed by October instead of September. What am I doing wrong?

Paul McGuinness

Hi @axelrodo

Interesting issue, i had the same results but overcame it by adding a helper column on the main sheet that holds the corresponding month number for that row, Jan -1, Feb = 2 etc.

Include that column in the report and you can then sort your results as wanted, you can hide the column or omit it from any dashboard tables etc.

Not the ideal solution but hopefully will do the trick for you?

Thanks

Paul

Andrée Starå

Hi @axelrodo

I hope you're well and safe!

To add to Paul's excellent advice/answer.

Another way and what I'd recommend changing the format to 01 - Jan, 02 - Feb because otherwise, when you com to Nov and Dec it wouldn't sort correctly.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul McGuinness

Hi @axelrodo

Interesting issue, i had the same results but overcame it by adding a helper column on the main sheet that holds the corresponding month number for that row, Jan -1, Feb = 2 etc.

Include that column in the report and you can then sort your results as wanted, you can hide the column or omit it from any dashboard tables etc.

Not the ideal solution but hopefully will do the trick for you?

Thanks

Paul

Andrée Starå

Hi @axelrodo

I hope you're well and safe!

To add to Paul's excellent advice/answer.

Another way and what I'd recommend changing the format to 01 - Jan, 02 - Feb because otherwise, when you com to Nov and Dec it wouldn't sort correctly.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

axelrodo

Thanks folks, that confirmed the way I started to work on as I couldn't find any alternative. So, definitely the only route to success. Have a good week too!😁

Andrée Starå

@axelrodo

Excellent!

Happy to help!

hdierkers

Wanted to add a question to this - how would you then sort across years? I have helper columns to pull out the month name, the month number, and the month + year (all pulled from the due date) and I can't seem to get it to group/sort correctly!

Andrée Starå

Hi @hdierkers

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

hdierkers

Here are some screenshots:

When I put into a report this is what I get, but I want it to go in chronological order from 3/2023-01/2024. Ideally I'd like to have them grouped by Month names using the If Month = 1,"January", etc. formula, but I'll take whatever I can get to make it sort the right way!

Andrée Starå

@hdierkers

Ok. Then you would need to add a year and month column.

Something like this.

Year

=IFERROR(YEAR(ColumnName), "")

Month number and Month name

=IF(ColumnName@row <> "",

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

Did that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

hdierkers

Yeah, this works - I can group by Year then by Month column - thanks!

Andrée Starå

@hdierkers

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Julie Becker

https://community.smartsheet.com/discussion/comment/418826#Comment_418826

here is what i had to do (see image below on visual), then in reports / sheets, you can sort using the helpers. I have 4 of these groups in 1 sheet pulling from columns that have links/formulas in them due to sorting issues.

Helpers for breaking down the Month and Years for multiply types of reports. For "Insert Col. Name Here" replace with your columns name using note/word program by Find/Replace for quicker updating all.

Cutover Helper: 📅 Month Tag Column: Formula Below ⬇️

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

Cutover Helper: 📅 Month# Column: Formula Below ⬇️

=MONTH([Insert Col. Name Here]@row)

Cutover Helper: 📅 Year# Column: Formula Below ⬇️

=YEAR([Insert Col. Name Here]@row)

TEXT TO DATE Helper Column: Formula Below ⬇️ (Converts a text date to a actual date)

=DATE(VALUE(RIGHT([Insert Col. Name Here]@row, 2)) + 2000, VALUE(LEFT([Insert Col. Name Here]@row, 2)), VALUE(MID([Insert Col. Name Here]@row, FIND("/", [Insert Col. Name Here]@row) + 1, 2)))

1st image below shows the formula above ⬆️ in action converting data shown in the 2nd img. Then the helpers (RELO Month Tag, Month#, Year#) breaks it all down.

im sure this is the messiest way to do this, but it works, and to me thats all that matters on some of our sheets that is a formula nightmare from past setups (not going to redo all that work). So i used helpers to tear apart problem columns for reports, graphs, metrics and sorting. Hope that helps you / anyone else here 😀