topic Re: Testing a date for being a working day in QlikView

Testing a date for being a working day

Thu, 12 Sep 2013 10:39:30 GMT

I want to represent some data in a calendar style and want to show the non-working days as greyed out. I have the data displaying OK but cannot crack the weekend bit. I'm not too worried about bank holidays.Any ideas?

Re: Testing a date for being a working day

Thu, 12 Sep 2013 10:53:30 GMT

Hi,

Please attach an example document?

Thanks

Stefan

Re: Testing a date for being a working day

techvarun — Thu, 12 Sep 2013 11:01:23 GMT

this link may help http://community.qlik.com/thread/86031

Regards

Re: Testing a date for being a working day

techvarun — Thu, 12 Sep 2013 11:02:53 GMT

Please read this blog post http://yahqblog.blogspot.in/2010/09/qlikview-working-day-functions.html

you will get an idea

Regards

Re: Testing a date for being a working day

Thu, 12 Sep 2013 11:29:12 GMT

Networkdays gives me the number of working days between two dates, I'm OK with that but what I want is whether a specific date is a working day. I think I can work with weekday(date) but if I apply that to my model it only gives a day number where I have an entry in the source data, Ideally I want to set the colour of the cell for each weekend day to be grey.

I've attached the model to the original post the offending chart is on the second tab.

Re: Testing a date for being a working day

Thu, 12 Sep 2013 11:41:41 GMT

Guess you could change the background colour of your dimension/expression (open the extra options within dimension/expression: +)

Adding an if statement using the weekday(date) should be enough

Re: Testing a date for being a working day

Thu, 12 Sep 2013 11:47:10 GMT

It says access denied when trying to open it?

Re: Testing a date for being a working day

Thu, 12 Sep 2013 11:53:41 GMT

Sorry yes it's set as having section access try this one?

I'm getting closer in using weekday to set the background color of the expression but that only works for the cells where there is data in the source file.

Try again

Re: Testing a date for being a working day

Thu, 12 Sep 2013 11:58:56 GMT

It still says access denied?

Re: Testing a date for being a working day

Thu, 12 Sep 2013 12:11:10 GMT

Hope this one will work

Re: Testing a date for being a working day

Thu, 12 Sep 2013 12:12:20 GMT

HI Colin,

Please try creating a variable :

LET vDate =weekday(date(YourDateField()))

Then an If Statement like example: If($(vDate) = 'Sat',LightBlue()).

Hope it helps.

Stefan

Re: Testing a date for being a working day

Thu, 12 Sep 2013 12:52:19 GMT

Hi Stefan

This is what I've done

=if(weekday(makedate([Absence Year],[Absence Month],[Absence Day])) ='Sat', green() ,white())

but still only highlighting those cells where there is source data.

Re: Testing a date for being a working day

Thu, 12 Sep 2013 13:06:54 GMT

Try :

=if(weekday(makedate([Absence Year],[Absence Month],[Absence Day])) ='Sat' or

weekday(makedate([Absence Year],[Absence Month],[Absence Day])) ='Sun', green() ,white())

Re: Testing a date for being a working day

Thu, 12 Sep 2013 13:17:13 GMT

As you can see it is correctly highlighting where there are entries but for the rest of the chard we background is still light blue. I'd like to show weekends as green throughout the chart like it would be on a calendar.

Thanks for your efforts so far, keep them coming