Average Period -> Working days

[#1]

Hi all !

I'm again sorry for the bad english : I'm french ^^


I have to calculate the average period between bug's creation and bug's resolvation. For the moment, I calculate this period with week-end. But i have to calculate this only with working days (without Saturday, Sunday, and public holiday).

For each bug, I have its creation date, and its resolvation date (if this bug is "Resolved").

I had this expression : AVG({<Etat={"Resolved"}>} [Resol_date] - [Create_date])


I have access to a calendar (ID_DATE, ID_WEEK, ID_MONTH, YEAR, DAY_TYPE) where DAY_TYPE can be "Work_day", or "Week_end" or "Pub_holiday". And to a table with columns : ID_DATE, ID_BUG, State where State can be "New", or "Resolved".

I want my period is calculated for each bug which are "Resolved" for today, for the selected date, and for the selected period (weeks, months, and year) by knowing that bug's states for a period are states the last day of this period.


I tested :

=avg({<State={"Resolved"}>} ([Resol_date] - [Create_date] ))
- count({1 + <State={"Resolved"}>} IF(ID_DATE>=date([Create_date]) AND ID_DATE<=date([Resol_date]) AND DAY_TYPE<>'Work_day' , ID_DATE))
/ count({<State={"Resolved"}>} DISTINCT ID_Bug)

- AVG returns the resolvation average period (with week ends, public holidays, ...)
- 1st count must return number of days on week-end or public holiday for all bugs between their creation date and their resolvation date
- 2nd count return number of bugs "Resolved" (according to acivre selection).

The first count returns bad results ...

Thanks a lot for help me ! If you had any questions, I'm here.

Thanks,
Pierre C.

[tresesco · Accepted solution]

Have a look at NETWORKDAYS() function. this might be helpful for you.

Regards, tresesco

[#3]

Hello Pierre,

I can see in the first count that you mix up SET Analysis and an IF()-clause. This is possible but if you think this is your misleading expression I wouldn't mix it. What about counting with an IF() similar to this:

count( IF( State="Resolved" AND ID_DATE>=date([Create_date]) AND ID_DATE<=date([Resol_dat .....

HtH

Roland

[#4]

Hi !

Thanks a lot for your help tresesco and Roland.

I think i already tested the IF()-clause you propose to me.
But I tested the NetWorkDays() function (which I didn't know) and results are good 😃

--> =AVG({<State={"Resolved"}>} NETWORKDAYS([Create_date], [Resol_date])-1)

Thank you for your quick help 🙂