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: 
Anonymous
Not applicable

Displaying shift plan

Hello Dear Community

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

shift_Data.png

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:

pivot.png

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

5 Replies
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

Anonymous
Not applicable
Author

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

avinashelite

what if you use a simple blank like this

if(Dienststart.Code_Nr=0,'  ', Sum(Dienststart.Duration)) this doesn't serve the purpose ??

Anonymous
Not applicable
Author

Hi tim

Thanks a lot for your effort. Looks amazing! This will help me a lot. One last question: Wehre did you definde the Event which changes the shifts? I can't find it.

Best Regards

Anonymous
Not applicable
Author

Stefan,

I just used a document event trigger -> OnAnySelect, using the UPDATE function with an if statement and a variable containg the number of selected shift/person combination.  After update, release the selection just made by BACK action. 

Regards

Tim