Be Careful with Week Numbers – Week is not Weak!
Be Careful with Week Numbers – Week is not Weak!
As corporations rely more and more on data driven business decisions and, try to identify time based patterns in their business data sets to predict the future, it is very important to understand how week numbers can easily trip you up when you compare data between weeks year over year or in any time shift period.
A date in current year can be in one week number, but the same date can be in a different week number last year or two years back or years ahead in the future. For example:
DateISO Week Num9/28/2020409/28/201939
In Excel you can use the “=ISOWEEKNUM()” function for a given date and it will provide the given ISO week value.
As you can see, 28th of September in 2020 is in week 40 and in 2019 week 39. So, if my CFO asks me for a business report for current year’s week 40 financial reports to last year and wants to filter the report using dates and I don’t accommodate the fact the 9/28/2019 is in week 39 of last year, I would include last year week’s 39 data into current year’s week 40 data.
In the modern world, we have plethora of tools available to do time series analysis. Starting from spreadsheet tools like Microsoft Excel to database tools like SQL to data programs like Python / R to Business Intelligence tools like Power BI / Tableau. All these tools can do time series analysis via simple line of codes or complex nested formulae. But all these tools use calendar dates to do time series analysis, where the tool can easily trip over date to week issues as mentioned in the above example.
Data Analysts / Data Engineers / Data Scientists / BI Analysts – All Data Geeks need to be very careful in constructing their time series algorithms / functions to be accommodative of this problem.
To present an industrial example, the following snippet is a Sales representation for week 40 of year 2020 compared to week 40 of 2019 (LY). You can see the error in Sale LY (dark green graph) when I am not considering the fact that dates can be in different weeks year over year and cause lying comparative data.
Copyright Advanced Grower Solutions
The Data Representation is Proprietary and Protected.
The problem can be solved in different ways based on the tool you are using for your data analysis and reporting purpose.
Advanced Grower Solutions specializes in business data analytics by using tools like SQL, Python, Power BI and Microsoft Excel. We hold expertise in solving data problems like this. We spend time to understand your business requirements, spend time to understand your data and make sure that the data does not lie to you, because it can very easily.
Please contact us if you want to discuss data and solutions.