Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mp802377
Creator II
Creator II

Line color based on weeks.

I have this chart below.

original chart.JPG

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:

after update chart.JPG

What did I do wrong?

Thank you for the help!

Martha

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

View solution in original post

9 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

mp802377
Creator II
Creator II
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

...

johnw
Champion III
Champion III

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.

mp802377
Creator II
Creator II
Author

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)

:

using weekstart.JPG

Thank you

johnw
Champion III
Champion III

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.

mp802377
Creator II
Creator II
Author

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.

johnw
Champion III
Champion III

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

mp802377
Creator II
Creator II
Author

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