I think you should change the order of your IF() conditions. Currently, all prior weeks satisfy the IF condition for the first green color, and none of the following conditions are being evaluated. Something like this should work:
Having said that, you should probably enhance your conditions a bit, to account for the following:
1. It looks like the GREEN60 condition works for the week that's before the prior week. You might need to use "Greater or equal" comparison to get prior week.
2. If you make that change, then the Black color condition is not going to materialize - all the older weeks will remain Blue30. You may want to add a second condition to limit the last color before Black
3. Once you turn into the new year, the week comparisons will break. In order to account for year-end, it's better to compare dates, such as WeekStart().
Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!
In order to use WeekStart, you should prepare your [Week] field accordingly - use a Dual value that holds the WeekStart() date as a number and the desired week format as a text. In that case, if the [Week] Field is a dual field, you could use it like this:
Or perhaps some expression like this:
pick(1+(weekstart(today())-[Week])/7, vCOLOUR_BLACK, vCOLOUR_BURGUNDY, vCOLOUR_GREEN60, etc.)
Alternatively consider creating a WeeksBack field and mapping it directly to colours.
,rgb(R,G,B) as WeekColour
Then just use WeekColour as your background colour. That's what I'd likely do myself.
I tried this because I like how clean it is. but I am having some issues. I am fairly new to Qlivkiew. My variable for week is vWeek = Date(WeekStart(Date, 0, -1), 'DD-MMM-YY') , this is also used for my field setting so that way I can start the week on Sunday. Week did not work in the formula, but Date did. The problem is Sunday is one color then the remainder of the week is another.
My dimension for Week is: =Date(WeekStart(Date, 0, -1), 'DD-MMM-YY') so that way I start it on Sunday (which is what they want). How do I fix? I tried putting a -1 after (pick(1+((weekstart(today(),0, -1)-[Date])/7)) to get it to Sunday, but that didn't work
This is the formula that I have now:
pick(1+((weekstart(today())-[Date])/7), vCOLOUR_RED, vCOLOUR_GREEN60, vCOLOUR_DARKGREEN40,
OK, weeks starting on Sunday instead of Monday, and you already understand how to do that in the weekstart() function. Do you have Week as a field rather than just a calculated dimension? It works regardless, but I always prefer real dimensions to calculated dimensions where practical. So I'd have a Week field defined as date(weekstart(Date,0,-1),'DD-MMM-YY'). And that may be what you meant. At that point, I think we'd want this:
But you're saying that Week didn't work. Well, if Week is a real field, but defined in some other way than shown above, that could be why the expression wasn't working with Week in it. But I'm not clear how Date would work at all, since for a given week, there could be multiple Dates, and thus a reference to Date would be expected to return null in those cases. And even if there was a single Date for the week, unless it was always Sunday, you'd need to weekstart(Date,0,-1), or plug in your vWeek.