9 Replies Latest reply: Dec 29, 2016 2:50 PM by Martha Parsons RSS

    Line color based on weeks.

    Martha Parsons

      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

        • Re: Line color based on weeks.
          Oleg Troyansky

          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.
              Martha Parsons

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

                • Re: Line color based on weeks.
                  Oleg Troyansky

                  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.
                      John Witherspoon

                      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.
                          Martha Parsons

                          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

                            • Re: Line color based on weeks.
                              John Witherspoon

                              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.