Thanks PRAYSON WILFRED DANIEL for your reply. Yeah, this is a good formula if i want to run for a specific date. in my case, i want this applied to each week of creation date. the sql command provided is for showing the logic for a certain week.
How would you define week of creation? Is it BETWEEN [Creation Date] AND DATEADD('day',7,[Creation Date]) ?
In the attached workbook above, i have created two fields from "creation_date" , named "start date" and "end_date". those have the formula to define a week range.
start date = date(DATETRUNC('week',[Creation Date]))
end date = Date(DATEADD('day',6, DATETRUNC('week',[Creation Date],'Sunday' ) ))
the thing here is that i have to go back and sum the counter for each status change that was done before the end of each week. that would get the open issues count correctly.
Thanks for the efforts. the end result i wanted is like this :
week of creation_date created_issues open_till_end_of_week
================== =========== ================
5-mar-2017 2 1
12-mar-2017 3 2
19-mar-2017 1 2
I think (big caveat!) it's possible...how do you handle the cases of, C, where it Closes in it's Creation Week, but then is re-opened. It is then re-closed again the following week. The LoD I was thinking would pull the last close date, but if issues are re-opening and closing...it may need something more exotic (...a table calc!)
When i applied this to live data, i got inconsistent results. Attached is the new files of live data.
The result should be:
Start Date still open still open issues
======= ====== ============
10-aug-14 1 iam-65
19-Oct-14 1 iam-443
26-Oct-14 1 iam-443
but i am getting :
I tried playing around with the calculations, but i am not able to get the correct result. Any ideas please ?