Forum Discussion

MichaelDano's avatar
MichaelDano
Brass Contributor
Mar 15, 2023

Sharepoint list does not group correctly on a calculated field

I have a Sharepoint list with a field "Day", where users enter a date.

I have a calculated field, "Week", that finds the week number of "Day".

When I group by "Week", the list only shows the first value of "Week" (week 15) although it holds entries for several weeks.
I have no filteres applied.

If I change the list to group by another field, e.g. "Day", it works find.

I suspect there is a bug, when grouping by calculated fields?


Any comment is more than welcome.

Thanks
Michael

 

microsoft lists
SharePoint Online

6 Replies

  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Mar 16, 2023

    Hi MichaelDano 

    grouping by a calculated field works in general... See here

    Could you share the formula you used in the calculated field?

    Did you group the view using the arrow down next to the column header or did you edit the view in classical mode using "Edit current view" and then scrolling down to "Group By"?

    Best Regards,
    Sven

    • MichaelDano's avatar
      MichaelDano
      Brass Contributor
      Mar 16, 2023
      Hi SvenSieverding

      The week number formular (with ";" and not "," which work for my location in DK):
      =CONCATENATE(TEXT(IF(INT((Day-DATE(YEAR(Day);1;1)+(TEXT(WEEKDAY(DATE(YEAR(Day);1;1)-1);"d")))/7)=0;52;INT((Day-DATE(YEAR(Day);1;1)+(TEXT(WEEKDAY(DATE(YEAR(Day);1;1)-1);"d")))/7));"0"))

      I'm using modern Sharepoint and edited the view, scrolled down to grouping and set "Week" as group by..

      I've now tried the group by function next to the column header, and that didn't work either ...

      I'm not sure why I have to use ";" instead of "," in my formulas, but guess it must have something to do with local settings?
      • SvenSieverding's avatar
        SvenSieverding
        Bronze Contributor
        Mar 16, 2023

        Hi MichaelDano,


        I'm not sure why I have to use ";" instead of "," in my formulas, but guess it must have something to do with local settings?

        It is the same with german local setting. I think it is because we use the comma as a separator in a decimal number and not the dot... So we can't use the comma a parameter-seperator in a formula and have to use the semicolon.....   

        Nevertheless.... I used your formula in a calculated column and set the grouping as you described.
        All rows were grouped nicely, so i could not reproduce your issue.

        Could you create a brand new view with just the fields "Title","Day" and "Week" and check if this issue occurs there, too?

        Best Regards,
        Sven
         

Resources