Calc by week then day of week using fiscal date table

[#1]

The fiscal date calendar has the following fields "Day YYYYMMDD", Day of Week, Month, Day of Month, Year

What i'm trying to do is do a comparison to the same week, then day of week as this time last year, then to 2009 to 2008. However, the problem is that in 2008 the fiscal date calendar had 53 weeks instead of 52. So my weeks/days do not line up properly.

Does anybody have any advice on the best way to possible write this in an expression to be able to have YOY comparisons? IF you need more info please let me know.

Thank you,

[Anonymous]

Oh, that's not the real year and week - and it is easier. You don't need to know the ISO week number at all. Just compare the days of current week against the days 364 days back (52*7*1) and 728 days back (52*7*2)

[yblake]

I understand that it may not sound obvious for users that first days of years fal in week 53, and this can have quite awfull side effects i.e. in sorted graphs or arrays.. (the week 53 is at the end of the table)

A "quick and dirty" option in that case is to assimilate that theses days are in week 1, meaning the first week of year may contains more than seven days.You can use this formula :

load ... mod(week(<your date>)-1,52)+1 as <your week>

and assume that you now have some elastic weeks... i.e. first week of 2010 will haveTEN days, comparing to 2009 that would had only FOUR days...

You can check with that code :

calendar:

load
date(makedate(2009,1,1) + rowno() - 1) as MyDate
autogenerate
(makedate(2010,12,31) - makedate(2009,1,1) + 1)
;

left join (calendar) load
MyDate,
week(MyDate) as ISOWeek,
mod(week(MyDate)-1,52)+1 as FakeWeek
resident
calendar
;

this can do the trick in some statistics comparison, but for finance it's sounds better to rely on a fiscal calendar method agreed with users.