if date within last 2 days of Month end

[#1]

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

[Anonymous]

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.

[#3]

Perhaps you can use the MonthEnd() function and subtract two days.

[#4]

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

[#5]

sorry meant to say the above is an expression

[#6]

MonthEnd() needs an expression/field. For instance MonthEnd(Today()) or MonthEnd(Orderdate).

[Anonymous · Accepted solution]

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?

[#8]

Thanks guys

The above works a treat