7 Replies Latest reply: Apr 26, 2011 10:23 AM by Anne Duffy

# 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

• ###### if date within last 2 days of Month end

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.

• ###### if date within last 2 days of Month end

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

• ###### if date within last 2 days of Month end

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

• ###### if date within last 2 days of Month end

sorry meant to say the above is an expression

• ###### if date within last 2 days of Month end

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

• ###### if date within last 2 days of Month end

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?

• ###### if date within last 2 days of Month end

Thanks guys

The above works a treat