5 Replies Latest reply: Oct 31, 2017 1:43 PM by Tim Stemmer RSS

    Displaying shift plan

    Stefan Habermacher

      Hello Dear Community


      I have to display a part of a shift plan. The data looks like this:


      So I have three different "shift-types", 4F, 4M, and 4S. Each date (day) can have 0..n shifts always assigned to one person (PERS_NAME_VOLL). I added the duration field as 1 for each data set.


      I'm not yet sure how to display this. So far I think a pivot table would work best, however, it has its restriction. This is what I came up with:


      The names on the left, then all three shift types each day. What I don't really like:

      You can't really "separate" each day with a bold line or something like that. Also, I would to change the background color of the fields:

      I'll define a varibale: LET v4F = '2';  -> Meaning: Each day has to have at least two 4F Shifts. If this is the case, to Total row should have a Green background, if not then it should be red.

      If the total amount of shifts is met, all the 1 values should have a green background, the 0 values a grey background. If the total amount of shifts is not met, all the 1 values should have a green background, all the 0 values a red background.


      I have currently three expressions with set analysis instead of a Shift Type dimension:expressions.png


      This is just because that way I can set the background color per expression and this might make it easier.



      Has anyone a better idea how to display this? And is the "background color thing" even possible?


      Thanks and best Regards

        • Re: Displaying shift plan
          Sunny Talwar

          I don't think the separator will be possible, but may be add a blank 4th expression...and color it all black to give a somewhat separator feel. Also, color should be possible using if statement

            • Re: Displaying shift plan
              Tim Stemmer

              !Attached a short document that should do what you request.


              The "Seperator" Column is an additional 4th expression, there is no way in Qlik to change the line between some columns.  I use the "|" as item to show in each line, looks a bit like the seperator columns in good old times where we used Matrix printers.


              The color part is a bit more tricky, I included the default numbers per shift into the model as a field, not as variables.  Doing this, you could use it in the background color definition of the expression.  The default numbers could be changed (Inputfield).  As the fields in your Data with no shift assignment are Null values, the could not be selected in the pivot table as well it's difficult to assign the background color.  I added in the script a part to fill all possible fields with "0" if missed, so that there is a a value in every cell of your pivot.  Doing this, clicking on the pivot will change the status auf a person in a shift, triggered by a selection event.  Maybe you could use this in your project.


              Edit by moderator:

              Please attach a document directly to a post instead of uploading as a separate document. You can do so using the "attach" option. To find that option, click on "Use advanced editor" in to the-right when writing a reply.

              I made the correction to this post for you.