How to manage weeks of different years

Question

Hello,

I need to compare numbers from same ISO weeks (beginning on monday) between two years.

Example :

Iso Week 28 - In 2023 from 10 to 16 of july - In 2022 from 11 to 17 of july

Iso Week 29 - In 2023 from 17 to 23 of july - In 2022 from 18 to 24 of july

When using "WEEK_OF_YEAR(expr)", the result for week 28 is :

9 to 15 in 2023 and 3 to 9 in 2022 ... so absolutely not the desired result !

So this is not the ISO week of year mechanism (but the US one).

Any idea how to solve my problem.

Regards.

Jean-Pierre

Gianni Ceresa · Accepted Answer

For any time analysis like this one, you should provide your own weeks, because every company has different meaning of something as subjective as the week.

You should have a time dimension containing all the required information based on your own definition of what the weeks should be like.

If you do want to dynamically calculate things with a formula, the formula will probably be quite complex and wrong every year because it isn't your definition of week, it's the definition of somebody else.

EzequielC-Oracle · Accepted Answer

Hi Jean-Pierre,

You could enable the EVALUATE predicate in System Settings -> Evaluate Support Level and invoke a database function that shows the ISO week. For example in the Oracle database you can issue:

SELECT TO_NUMBER(TO_CHAR(CAL_MONTH_END_DATE, 'IW')) FROM BISAMPLE.SAMP_TIME_MTH_D;

where

CAL_MONTH_END_DATE is a date column data type and

IW is the format mask that returns the ISO week number of the year

So above query returns the ISO week number for the date column.

Create a new calculation in DV to use the function inside the EVALUATE predicate. Example:

EVALUATE('(CAL_MONTH_END_DATE, "IW"')

Here is the documentation link for the evaluate functions:

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/functions.html#GUID-7035040C-BB40-4392-920A-9A435593F659

This a video on how to create calculations to use database functions in DV:

https://www.youtube.com/watch?v=kOuJ8FUH_R8

Regards,

Ezequiel.

Gianni Ceresa · Accepted Answer

Ezequiel is assuming that you have a database. The EVALUATE function is pointless if there isn’t a backend to push down the formula to, while your own time dimension is just a join away (on the date) from any source you are using.

Also a detail: the Oracle database can give you back about 3 if not more weeks calculated from a date. They all works slightly differently. You just make sure to test the formula you are going to use for all the dates you have already and all the dates you are going to have in the future to make sure it is exactly what you want and that it doesn’t change behavior on a year or 2 because of the date the year started etc.

Last, you will write a formula for your own analysis work. Your colleague will do the same but picking a slightly different formula existing in the database to calculate a week from a date. And that’s how you start comparing different things and guessing why your numbers make no sense across the company.

A common, shared, date dimension is just a good practice for analytics. Not only because it allows you to implement business rules like the meaning of weeks, but also for example work days and any other business rule applied to your dates.

And it can be used by everybody: in your RPD, in any dataset, including Excel files, just by joining on the date.

Not saying that EVALUATE isn’t able to give you back a week number (as long as you have a database behind your dataset), but carefully evaluate the downside and all the opening for future problems (including users who start abusing EVALUATE when they discover it’s on and will kill your database with the most exotic kind of queries, just because).