topic Line color based on weeks. in QlikView

Line color based on weeks.

mp802377 — Wed, 28 Dec 2016 21:02:08 GMT

I have this chart below.

I want the previous week to be Burgundy, the week before Green60 and so on. I put below in the expression under Background Color for the line.

If(Week(Today())=[Week],vCOLOUR_BURGUNDY,

If(Week(Today()-7)>[Week], vCOLOUR_GREEN60,

If(Week(Today()-14)>[Week], vCOLOUR_GREEN30,

If(Week(Today()-21)>[Week], vCOLOUR_AQUA60,

If(Week(Today()-28)>[Week], vCOLOUR_AQUA30,

If(Week(Today()-35)>[Week], vCOLOUR_BLUE60,

If(Week(Today()-42)>[Week], vCOLOUR_BLUE30,

vCOLOUR_BLACK)))))))

This is what I get:

What did I do wrong?

Thank you for the help!

Martha

Re: Line color based on weeks.

Oleg_Troyansky — Wed, 28 Dec 2016 21:15:11 GMT

I Martha,

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:

If(Week(Today())=[Week],vCOLOUR_BURGUNDY,

If(Week(Today()-42)>[Week], vCOLOUR_BLUE30,

If(Week(Today()-35)>[Week], vCOLOUR_BLUE60,

If(Week(Today()-28)>[Week], vCOLOUR_AQUA30,

If(Week(Today()-21)>[Week], vCOLOUR_AQUA60,

If(Week(Today()-14)>[Week], vCOLOUR_GREEN30,

If(Week(Today()-7)>[Week], vCOLOUR_GREEN60,

vCOLOUR_BLACK)))))))

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().

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

Re: Line color based on weeks.

mp802377 — Wed, 28 Dec 2016 21:18:52 GMT

Thank you so much! How would the WeekStart() look? Instead of using Today()?

Re: Line color based on weeks.

Oleg_Troyansky — Wed, 28 Dec 2016 21:21:49 GMT

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:

If(WeekStart(Today())=[Week],vCOLOUR_BURGUNDY,

If(WeekStart(Today()-42)>[Week], vCOLOUR_BLUE30,

...

Re: Line color based on weeks.

johnw — Wed, 28 Dec 2016 21:34:00 GMT

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.

WeekColours:
LOAD *
,rgb(R,G,B) as WeekColour
INLINE [
WeeksBack,R,G,B
0,0,0,0
1,200,50,100
2,100,200,100
etc.
];

Then just use WeekColour as your background colour. That's what I'd likely do myself.

Re: Line color based on weeks.

mp802377 — Thu, 29 Dec 2016 17:42:36 GMT

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,

vCOLOUR_AQUA60,vCOLOUR_BURGUNDY50, vCOLOUR_BLUE60,vCOLOUR_DARKGREEN10,

vCOLOUR_YELLOW,vCOLOUR_DKGREY,vCOLOUR_LTGREY60)

Thank you

Re: Line color based on weeks.

johnw — Thu, 29 Dec 2016 18:06:31 GMT

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:

pick(1+(weekstart(today(),0,-1)-[Week])/7,...colors...)

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.

Re: Line color based on weeks.

mp802377 — Thu, 29 Dec 2016 19:03:28 GMT

I have week in my calendar (in the Script Editor) as:

Week(Temp_Date) as Week,

I have a variable setup vWeek as: WeekStart(Date, 0, -1).

When I try pick(1+(weekstart(today(),0,-1)-[vWeek])/7, it does not work and when I try Week it does not work either. That is why I tried Date just to see.

Re: Line color based on weeks.

johnw — Thu, 29 Dec 2016 19:08:22 GMT

OK, then I would add this to the calendar:

weekstart(Temp_Date,0,-1) as WeekStart,

And then try this:

pick(1+(weekstart(today(),0,-1)-WeekStart)/7,...colors...)

Re: Line color based on weeks.

mp802377 — Thu, 29 Dec 2016 19:50:26 GMT

That was it. Thank you so much for the help.