topic Re: Displaying shift plan in QlikView

Displaying shift plan

Anonymous — Thu, 26 Oct 2017 13:20:38 GMT

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:

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 — Thu, 26 Oct 2017 14:31:56 GMT

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

Anonymous — Sun, 29 Oct 2017 18:51:42 GMT

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

Re: Displaying shift plan

avinashelite — Mon, 30 Oct 2017 09:29:03 GMT

what if you use a simple blank like this

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

Re: Displaying shift plan

Anonymous — Tue, 31 Oct 2017 17:31:27 GMT

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

Re: Displaying shift plan

Anonymous — Tue, 31 Oct 2017 17:43:59 GMT

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