Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
Perhaps you can use the MonthEnd() function and subtract two days.
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
sorry meant to say the above is an expression
MonthEnd() needs an expression/field. For instance MonthEnd(Today()) or MonthEnd(Orderdate).
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?
Thanks guys
The above works a treat