
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if date within last 2 days of Month end
Hi Guys
Hope someone can help.
I am calculating staff hours, on a normal working day we cout as 5.15 hours, and the last 2 days of the month we calculate as 7.15.
I have the charts very simply
Demension is [Date], and the Expression is: sum([hours])*5.15
But is it possibly to put a condition that states if date is equal to either of the last 2 working days mulitply by 7.15 ?
( I cant figure it out as every month has different number of days etc!!)
Thanks
Anne
- Tags:
- new_to_qlikview
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was thinking adding it as a flag in the load script with your other data, something like:
if (NetWorkDays(MonthStart(Date), MonthEnd(Date)) - NetWorkDays(MonthStart(Date), Date) <= 1, 'Y', 'N') as FlagLast2Days
0 and 1 would be the last and second last days. In your object, the if statement would be a lot easier now.
PS: By the way, I do not really understand why you sum the hours and then multiply this. Isn't the sum giving you the total work hours? Are you summing something else, e.g. days?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using networkdays in a condition. It returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays.
By knowing 1) the total number of workdays in the month and 2) the number of workdays from monthstart until date evaluated in the dimension, you could then test if the evaluated day in the dimension is one of the two last days.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps you can use the MonthEnd() function and subtract two days.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Guys
just trying the first part ( to recognise the Month end )
I have tried
if((Date)=MonthEnd(),sum([Hours])*7.15,sum([Hours])*5.15)
But now it actually is not showing any line in graph.
can you see anything obviously wrong , i can not figure it out
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sorry meant to say the above is an expression

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
MonthEnd() needs an expression/field. For instance MonthEnd(Today()) or MonthEnd(Orderdate).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was thinking adding it as a flag in the load script with your other data, something like:
if (NetWorkDays(MonthStart(Date), MonthEnd(Date)) - NetWorkDays(MonthStart(Date), Date) <= 1, 'Y', 'N') as FlagLast2Days
0 and 1 would be the last and second last days. In your object, the if statement would be a lot easier now.
PS: By the way, I do not really understand why you sum the hours and then multiply this. Isn't the sum giving you the total work hours? Are you summing something else, e.g. days?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks guys
The above works a treat
