Hi all,
I have an issue concerning the date table and weekly data. A lot of the data is based on week, so no dates in the dataset, only year and week number, following ISO. So with a normal date table and week calculation the week number will not always match these weekly data sources, for example an extra week 53, missing week 1, shift in week number throughout the year (+-1).
When changing it to WEEKNUM(Date, 21) it matches the weeks, but then what happens is that the first week starts in the previous year and then those data suddenly end up in week 1 for the previous year.
So I tried a weekly calendar in Bravo and everything works great. But then I get issues with other data that comes in on a continuous basis, every day. So monthly data is skewed by the weeks attributed to the month, quarter and year. So December might be missing some data in the start or end, and then the previous or next month will get too many days from other months, leaving this data to look zig zawy rather than smooth when on a day - month - year calendar.
Is there a way to get around this so that we can use the normal aggregations but still get the week number matching? With WEEKNUM(Date, 21) it seems like the issue is that week 1 will some years end up in the previous, so it might be enough to make a rule that if that happens it will be attributed to the next year, but not sure how to do that without skewing the other data.
Any feedback on this is much appreciated.
Best regards,
Johan Burrito
