Calculate week of year with weeks starting on Sunday's

Problem:

By default, day’s of the week is calculated starting from Sunday:

However, week of year is calculated starting from Monday:

When working and comparing results from previous reports, you may find discrepancies in your calculations if you’re expecting the week of the year to be calculated starting from a Sunday.

Solution:

In Arcadia, there’s a built in function in the Visual Builder (found in the Field Properties menu) to choose to calculate day of week starting from Monday when you select a field from your shelf:

If you look at the Custom Expression, you can see that the dayofweek() calculation is slightly modified for Sunday’s (where dayofweek() = 1):

If we follow some similar logic when calculating the week of the year, we can also adjust it so that each week is starting from a Sunday. In the screenshot below, we’re evaluating the dayofweek() to look for Sunday’s (where dayofweek() = 1), and for each record that meets that condition we iterate the week of year up 1 week:

Now if we compare our dates, days of the week, and weeks of the year, we can see that now both the default behavior of day of the week starting from Sunday lines up with the week of year calculation starting from Sunday:

1 Like