kqlazure-data-explorerkusto-exploreraskql

Kusto week_Of_Year returns unexpected week no if 1st day of 1st week of an year is Sunday


let dt = datetime(2023-01-01);
let weekOfYear = datetime_part("week_of_year", dt);
let year = datetime_part("year", dt);
print 
YearWeek = strcat(year, '-', weekOfYear)

Returns: 
YearWeek
2023-52

Expected: 
YearWeek
2023-01

Apparently 1st day of the first week of the year plays a major role in deciding whether or not this 'first day' falls part of the last week of the old year or the first week of the new year. Like stated in ISO 8601 but then the function year does not so if I want to concat the year and the week # it gets all mess up because year will always get the year in the date and will not be in accordance to weekofyear, which for JAN 1 might return a value of the past year.

Is there a specific function to deal with this or any feasible workaround??


Solution

  • No inbuilt function that I know of, but you could always manually calculate the week number from an arbitrary date. This is often useful when calculating Financial Weeks for example. If we take the same logic and calculate from the first day of the year could this work in your case?

    let dt = datetime(2023-01-01);
    let weekOfYear = datetime_part("week_of_year", dt);
    let year = datetime_part("year", dt);
    let getyearweek = (t:datetime)
    {
    let t1 = (datetime_diff('day', t, startofyear(t)) / 7)+1; //"startofyear(t)" represents the start of the time calculation
    iif(t1 >= 53, toint(52), toint(t1))
    };
    print 
      YearWeek = strcat(year, '-', weekOfYear),
      YearWeek2 = strcat(year, '-', getyearweek(dt))
    
    YearWeek YearWeek2
    2023-52 2023-1