Question

Business days calculation using Function alias in Report definition

Hi,

We have a requirement to display business days in a report definition.

we need to calculate the age as difference in business days. If the target date was Friday 4th Oct, if we run the function today we need to get the age as 10 as we need to exclude the weekends which are not business days.

Function BusinessCalendar is not supporting in RD. I had gone through PDN and Mesh, some posts are like creating DE for a property using BusinessCalander function. So that we can use that property. But in our requirement without property, we need to apply businessdays calculation to the required columns. I tried to create Function Alias rule, but some where I missed some logic. It’s not working for some scenarios.

Below is the logic of function :{2} -Is the calendar days(difference of days between Target data and Today)

CASE

WHEN {2}>=6 THEN {2}-(({2}/7)*2)

WHEN {2}>=0 AND {2}<6 THEN {2}

WHEN {2}>=0 AND {2}<6 AND DATENAME(weekday,{1}) = 'Sunday' OR DATENAME(weekday,GETDATE()) = 'Saturday' THEN {2}-1

WHEN {2}<0 THEN {2}+(({2}/7)*2)

END

Any one have function alias logic to get business days, please help me to solve the issue.

Thank you,

***Moderator Edit-Vidyaranjan: Updated Platform Capability***

Comments

Keep up to date on this post and subscribe to comments

October 18, 2019 - 3:57am

Why can't you create your own function alias for calculating business days?

October 18, 2019 - 5:33am
Response to vaspoz

Nanf

 

Hi,
I have created my own function alias by passing target date, and calendardays(difference of targetdate and currentdate) below is the code. It is not working for some scenarios, could you please help me to achieve this.

 

If target date is 22nd Oct and Current date is 18th Oct then result should be -1, it's not working for negative values

CASE
WHEN {2}>=6 THEN {2}-1-(({2}/7)*2)
WHEN {2}>=0 AND {2}<6 THEN {2}
WHEN {2}>=0 AND {2}<6 AND DATENAME(weekday,{1}) = 'Sunday' OR DATENAME(weekday,GETDATE()) = 'Saturday' THEN {2}-2
WHEN {2}<0 THEN {2}+1+(({2}/7)*2)
END

 

Thank you,

Nandini

October 18, 2019 - 5:12am

It may help if you could share what kind of database you're using.

October 18, 2019 - 6:25am
Response to LaurenceB_GCS

Hi ,

Our application data base is MS SQL.

Thank you,

Nandini.