Forum Discussion
MichaelDano
Mar 15, 2023Brass Contributor
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 ...
MichaelDano
Mar 16, 2023Brass Contributor
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?
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
Mar 16, 2023Bronze Contributor
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
- MichaelDanoMar 16, 2023Brass ContributorI've created a brand new view - same problem.
I've created a brand new list, with just these 3 fields (title, day, week) - and it works fine!?
On the first list (that doesn't work) I have some Power Automate on top of it. Could that conflict in any way?
Thanks again SvenSieverding- MichaelDanoMar 16, 2023Brass ContributorHi again
In the list that didn't work, I created a new calculated field "Week Number" and added the formular and created a new list ... and magic ... now it works!
Thanks Sven for pushing me into solution mode as I was giving up 🙂- MichaelDanoMar 16, 2023Brass ContributorHi again again
I can see now, that your suggestion on creating a new view will do the job. However, I didn't use the standard view in the first attempt, but based the new view on an existing view, which didn't work.
So creating a new view based on the standard view was the right solution. THANKS!