I have been working on an issue for a while and cannot seem to solve this issue, Hopefully somebody on here can help me with the problem. I have a large set of data, which has dates for all of the appointments made. I want to see which weeks of the month are the most busy, 1st, 2nd, 3rd, 4th, 5th, 6th. I have the code that is kind of able to do the job but I am not able to label the rows appropriately. As in, "1st week", "2nd week", "3rd week", "4th week", "5th week", "6th week" and show the correct counts of appointments for all the dates in the date range.
Below is all the code that i have tried and gotten close but not able to get it to produce the exact results I am looking for. Any help would be great!
=QUERY({Income_Dates, ARRAYFORMULA(IF(Income_Dates<>"", WEEKNUM(Income_Dates, 2) - WEEKNUM(DATE(YEAR(Income_Dates), MONTH(Income_Dates), 1), 2) + 1, ""))}, "SELECT Col2, COUNT(Col1) WHERE Col2 IS NOT NULL GROUP BY Col2 LABEL Col2 'Week of Month', COUNT(Col1) 'Count'")
=QUERY({Income_Dates, ARRAYFORMULA(IF(Income_Dates<>"", WEEKNUM(Income_Dates, 2) - WEEKNUM(DATE(YEAR(Income_Dates), MONTH(Income_Dates), 1), 2) + 1 & "st week", ""))}, "SELECT Col2, COUNT(Col1) WHERE Col2 IS NOT NULL GROUP BY Col2 LABEL Col2 'Week of Month', COUNT(Col1) 'Count'", 0)
=QUERY({ Income_Dates, ARRAYFORMULA( IF( Income_Dates >= DATE(YEAR(Income_Dates), 1, 1), CHOOSE( WEEKNUM(Income_Dates - DATE(YEAR(Income_Dates), MONTH(Income_Dates), 1), 1) + 1, "1st week", "2nd week", "3rd week", "4th week", "5th week" ), "1st week" ) ) }, "SELECT Col2, COUNT(Col1) WHERE Col2 IS NOT NULL GROUP BY Col2 LABEL Col2 'Week of Month', COUNT(Col1) 'Count'", 0)
