I have a rather complicated formula that I built some years ago with the help of an external consultant - and which has been working ever since - to (make a dimension of the cw and) account for the fact that in some years, the last days of Dec fall into the same week as the 1st of January, so any calendar naturally treats that week as cw_1 of the next year.
That formula, which has as I said been working fine for a few years, just failed this month. So I guess that there is something particular about this year - well, there is, the 1st of January this year was a Friday, so the calendar would treat this as cw_1. What the formula is supposed to do (it only considers the case that the 1st of January is a Sunday or now a Saturday) is to make such a week figure as cw_53 so that the formula (it contains a <= and a >=) will not fail.
That is, I guess, a general shortcoming of the formula - this year, the 1st of January was a Friday which isn't in scope, so it would have failed for that reason.
The error_message is different: "Garbage after expression 'Sun' " - which is strange because the day_names are set to that standard. I still believe it is connected to that general shortcoming of the syntax.
=> Can you help me replace that querying for specific weekdays with some code to just query whether the 1st of January falls into the same week as the 31st of December? Is that possible? That would make it easier - I don't know which days the 1st of January can fall on and what would happen then? If the 1st of January happened to be a Tuesday, would that be cw_1?
The complete code of the formula (sorry, the comments are in german) is as follows:
IF(
(Jahr = yEAR(TODAY()) // Wir wollen nur das aktuelle Jahr haben ...
// ... Die untere Grenze für ein Kalender-Monatsblatt ist die erste KW des Monats; Wenn der Monat an einem So angefangen
// hat, müssen wir zu der KW allerdings 1 dazurechnen.
AND (Woche_bereinigt >= if((weekday(MonthStart(today()))<>'Sun' and weekday(MonthStart(today()))<>'Sat'),
// Die obere Grenze des Kalender-Monats ist die letzte Woche des Monats - wenn allerdings diese (das kann nur im Dez
// passieren) in die KW_1 des neuen Jahres fällt (wenn der 01.01. in der gleichen Woche ist wie die letzten Dezember-
// tage) dann soll das KW_53 sein und nicht 1.
AND (Woche_bereinigt <= if(Week(MonthEnd(TODAY()))=1,53,Week(MonthEnd(TODAY()))))),
// ==>> Woche_bereinigt ist das eigentliche Dimensionsfeld, das wir hier haben wollen.
Woche_bereinigt)
// Das Gesums mit Woche_bereinigt haben wir für den Fall, dass die ersten Tage eines Jahres kalendermäßig in die letzte
// Woche des Vorjahres fallen.
P.S.: I guess in the newest version of QlikView, this problem doesn't even exist anymore, does it? When I type Week(Today()) now, it returns 1, the week of "6 days ago" figures as 53. So can I remove this entire crapheap?
I'm not sure if my week-approach here - Master Calendar with movable holidays - is the same like yours then I didn't consider any weekdays but maybe it's helpful for you to get some ideas.
I think I'm moving in the right direction with this. At least the dimensions seem to work again now.
What makes it complicated is that there are formulas for the background_colour of the cells, formulas for the text_colour and for the text_format as well, all of which are dependent on the values of the expression and have to fit each other.
