Skip to main content
Solved

How do I sort dates correctly in the Pivot block?

  • September 3, 2020
  • 4 replies
  • 36 views
Kim_Trager1
Forum|alt.badge.img+23

I got a bunch of values fro each collaborator in my base which I need to summarise pr month.

Down the ‘Y-Axis’ I got my collaborators.
My problem is I can’t get the ‘X-Axis’ - the month name to sort properly (see below).

Ideally I just want the month name showing, but it seems I can only sort those in alphabetical order.
Then I tried to add the month number to the month name in the hope it’ll sort on the month number, but as you can see December for some reason comes between July and September.

Is there no way that I can just sort the month names from Jan → Dec?

Best answer by ScottWorld

Months are a formular field with the following:

IF({Order date}=BLANK(), BLANK(), 
DATETIME_FORMAT({Order date}, 'M')& ' ' &DATETIME_FORMAT({Order date}, 'MMM') &  ' '  & DATETIME_FORMAT({Order date}, 'YY'))

As I can’t use a date field an only let it show the month name?

Tried to add a leading zero - however still doing the same. Ideally I’ll not have to lead with the month number.


It seems like you’re having sorting problems because of your formula.

Although you’re visually seeing the results that you want to see, you’re actually using the BLANK() and DATETIME_FORMAT() functions incorrectly.

This would be the best way to write your formula, and the results of this formula will sort correctly for you, too:

IF(
{Order date},
DATETIME_FORMAT({Order date},'MM MMM YY')
)

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • September 3, 2020

That’s odd. Is your “Month” field a date field? A text field? I would try toggling it between a text field and a date field to see which one sorts better, and also adding a leading 0 to the earlier months (01, 02, 03, etc.). See if any of that helps.

Kim_Trager1
Forum|alt.badge.img+23
  • Kim_Trager1
  • Author
  • Brainy
  • September 3, 2020

That’s odd. Is your “Month” field a date field? A text field? I would try toggling it between a text field and a date field to see which one sorts better, and also adding a leading 0 to the earlier months (01, 02, 03, etc.). See if any of that helps.


Months are a formular field with the following:

IF({Order date}=BLANK(), BLANK(), 
DATETIME_FORMAT({Order date}, 'M')& ' ' &DATETIME_FORMAT({Order date}, 'MMM') &  ' '  & DATETIME_FORMAT({Order date}, 'YY'))

As I can’t use a date field an only let it show the month name?

Tried to add a leading zero - however still doing the same. Ideally I’ll not have to lead with the month number.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • September 3, 2020

Months are a formular field with the following:

IF({Order date}=BLANK(), BLANK(), 
DATETIME_FORMAT({Order date}, 'M')& ' ' &DATETIME_FORMAT({Order date}, 'MMM') &  ' '  & DATETIME_FORMAT({Order date}, 'YY'))

As I can’t use a date field an only let it show the month name?

Tried to add a leading zero - however still doing the same. Ideally I’ll not have to lead with the month number.


It seems like you’re having sorting problems because of your formula.

Although you’re visually seeing the results that you want to see, you’re actually using the BLANK() and DATETIME_FORMAT() functions incorrectly.

This would be the best way to write your formula, and the results of this formula will sort correctly for you, too:

IF(
{Order date},
DATETIME_FORMAT({Order date},'MM MMM YY')
)

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Kim_Trager1
Kim_Trager1
Forum|alt.badge.img+23
  • Kim_Trager1
  • Author
  • Brainy
  • September 3, 2020

It seems like you’re having sorting problems because of your formula.

Although you’re visually seeing the results that you want to see, you’re actually using the BLANK() and DATETIME_FORMAT() functions incorrectly.

This would be the best way to write your formula, and the results of this formula will sort correctly for you, too:

IF(
{Order date},
DATETIME_FORMAT({Order date},'MM MMM YY')
)

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:


Thanks for this, I don’t know why I wanted to make that so complicated, however it still sorts as before.
However it still sorted as before, but realised when I made your above tweak and then it was the setting in my block that was wrong. I had column grouping set to value instead of group.

ScottWorld
Terms & ConditionsAccessibility statement