topic Re: Reference line/Grid for a day in a week in QlikView

Reference line/Grid for a day in a week

Anonymous — Wed, 16 Sep 2015 15:15:06 GMT

I'm trying to add a grid/reference line for every Monday for my Gantt chart.

The chart is similar to the one show blew. (Photo copied from Henric Cronström's post.Files for creation of a Gantt chart )

I would like either make the grid showing every Monday or add a reference line for every Monday.

Any idea how to achieve that?

Here's some sample data if you'd like to try.

Thanks!

sample data

Job ID	Time Started	Time Completed
1	August 16, 2015 at 9:54 AM	August 19, 2015 at 9:21 PM
2	August 20, 2015 at 5:55 PM	August 21, 2015 at 3:44 AM
3	September 8, 2015 at 4:49 PM	September 8, 2015 at 5:02 PM

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 16:08:58 GMT

one suggestion would be to write an expression using set analysis for day of the week is monday

weekday(date)

Week day. Returns an integer between 0-6.

Example:

weekday( '1971-10-30' ) returns 5.

If the date format used does not correspond to the one set in your operating system, QlikView will not be able to make a correct interpretation. See above under day(date).

Re: Reference line/Grid for a day in a week

Anonymous — Wed, 16 Sep 2015 16:16:51 GMT

Ok. After I get the workday(date). How do I add the reference line/grid to the chart?

Thanks

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 16:30:02 GMT

go to the Presentation tab for the chart and in the Reference Line section add the expression you just wrote

what you can do is create a variable that is the result of your date function and use the $ expansion for the variable in the reference line expression

in the attahced, my expression is my variable as described above

Re: Reference line/Grid for a day in a week

Digvijay_Singh — Wed, 16 Sep 2015 18:35:32 GMT

I tried like attached, but it is not showing reference line, may be the displayed date in Expression Axis by QV is not falling on Monday, but not sure of. This qvw could be used as starter for further R&D

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 18:50:59 GMT

as you can see in the attached, it does not look like there are any monday dates. Also, according to the documentation I was actually expecting a number (0-6) to be returned

Re: Reference line/Grid for a day in a week

Anonymous — Wed, 16 Sep 2015 18:57:20 GMT

I'm not sure if it matters whether my data has Monday or not. My goal is to have Mondays somehow indicated in the chart as a reference line or in the grid...

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 18:59:10 GMT

I agree

not sure why it is not working unless reference lines only work based on Expressions and not dimensions

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 19:00:21 GMT

It might have something to do with the X Axis option not being enabled so it is basing the line on the Y axis

Re: Reference line/Grid for a day in a week

Wed, 16 Sep 2015 19:20:57 GMT

it does not appear to be working the way I had hoped. I someone else can assist, it would be greatly appreciated

Re: Reference line/Grid for a day in a week

Digvijay_Singh — Wed, 16 Sep 2015 21:26:39 GMT

It seems reference line do appear if aggregation expression used or hard coded date as shown in the image. One important change needed from last document is to have format of expression as 'Date'. Didn't see any post where multiple ref lines are added like it requires here.