Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
The first count returns bad results ...
Thanks a lot for help me ! If you had any questions, I'm here.
Thanks,
Pierre C.
Have a look at NETWORKDAYS() function. this might be helpful for you.
Regards, tresesco
Have a look at NETWORKDAYS() function. this might be helpful for you.
Regards, tresesco
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
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 🙂