Sorting Norwegian weeknumbers (1 Viewer)

[boerbende]

Dear readers

I have a query get a weekaverage of a value.

SELECT Format([Testdate],"ww",2,2),2) AS wknum, Avg([testvalue]) AS AvgVar
FROM testtable
GROUP BY Format([Testdate],"ww",2,2),2)
ORDER BY Format([Testdate],"ww",2,2),2)

unfortunately, the 1-JAN-2017 is week 52.

How can I order this chronologically?
So the average of week 52 (only 1-JAN-2017) becoming the first point in a graph or list, before week 1 (which is 2-JAN-2017)

Many thanks

Ben

 

[sneuberg]

I don't know what the 2 means in the 4th argument (firstweekofyear) of

Format([Testdate],"ww",2[COLOR="blue"],[B]2[/B])[/COLOR]

but if you change it to 1 which is vbFirstJan1 the format will return 1 for #1/1/2017#

 

[boerbende]

That is correct but unfortunately not what is used in Norway. The tradition here is that the first week has to contain 4 full days. Hence the second 2 as argument.
I read it is mainly used in Norway and Sweden. And they use very often weeknumbers which, asking me, should be prohibited

Verstuurd vanaf mijn SM-G800F met Tapatalk

 

[sneuberg]

What else is going on in this query that forces you to stick to this tradition? What would go wrong if you changed the argument to 1?

 

[boerbende]

What else is going on in this query that forces you to stick to this tradition? What would go wrong if you changed the argument to 1?

As you wrote weeknr 1 for 1-1-2017 which is wrong. I tried it many times before in all ignorance but they always notice it
And with that the average over a week will be wrong as well, at least not consistent with historical calculations

Verstuurd vanaf mijn SM-G800F met Tapatalk

 

[JHB]

One way to get the sorting correct is to include the year in the weeknumber, like "2016-52", "2017-01" etc.
But the build in week function in MS-Access doesn't return the correct week number for certain dates, (like 31-12-2007), when the requirements are how the week numbers are calculated in Denmark, Norwegian, Sweden and some other countries.
I had the same problem for many, many years ago, but luckily for me I came across the below function in the Internet (function written by George Simms). After using this function my entire weeknumber problem was gone, maybe it can help you too.

Function WeekNrAndYear(InputDate As Long) As String
  Dim a As Integer, B As Integer, C As Long, D As Integer
  WeekNrAndYear = 0
  If InputDate < 1 Then Exit Function
  a = Weekday(InputDate, 1)
  B = Year(InputDate + ((8 - a) Mod 7) - 3)
  Aar = B
  C = DateSerial(B, 1, 1)
  D = (Weekday(C, 1) + 1) Mod 7
  WeekNrAndYear = Format(CStr(Int((InputDate - C - 3 + D) / 7) + 1), "00") & "-" & CStr(Aar)
End Function

 

[boerbende]

Perfect!
I used already years to sort correctly, but year(1-1-2017) was still 2017 and 2017-52 is still behind 2017-01

In the night I woke up with the idea to use weekday to find the year of the first day in the week because 1-1-2017 is weekday 7. And indeed the function from George Simms is using this!

Thank you very much

Ben

 

[JHB]

Perfect!
I used already years to sort correctly, but year(1-1-2017) was still 2017 and 2017-52 is still behind 2017-01

But 2017-52 is not correct - correct is 2016-52. And the function I posted will give you that.

 

[boerbende]

Correct conclusion
What I meant is that I always use yyyy-ww or yyyy-mm to sort weeks and months and indeed it went wrong with 1-1-2017. Hence the question in this forum

 

[JHB]

Got it!

 

[boerbende]

Det funker utmerket JHB. Tusen takk ;-)

 

[JHB]

Velbekom, held og lykke.