Hi all
Could I please get your assistance.
I have been googling and cant find anything that work. Thank you in advance for answering my query.
So basically, I need to count number of with Planned Finish Date between two date variables.
Here’s the data:
Execution Week | Planned Finish Date | Count | Work Order |
02/10/2017 | 2/10/2017 | 1 | Y0062595 |
02/10/2017 | 2/10/2017 | 1 | Y0062772 |
02/10/2017 | 4/10/2017 | 1 | 05326856 |
02/10/2017 | 4/10/2017 | 1 | 05326862 |
02/10/2017 | 4/10/2017 | 1 | Y0061138 |
02/10/2017 | 5/10/2017 | 1 | 05326803 |
02/10/2017 | 5/10/2017 | 1 | 05425384 |
02/10/2017 | 5/10/2017 | 1 | 05425385 |
02/10/2017 | 5/10/2017 | 1 | 05425390 |
02/10/2017 | 5/10/2017 | 1 | 05425395 |
02/10/2017 | 5/10/2017 | 1 | 05425409 |
02/10/2017 | 5/10/2017 | 1 | 05425419 |
02/10/2017 | 5/10/2017 | 1 | 05425420 |
02/10/2017 | 5/10/2017 | 1 | 05425421 |
02/10/2017 | 5/10/2017 | 1 | 05486740 |
02/10/2017 | 8/10/2017 | 1 | Y0062074 |
02/10/2017 | 8/10/2017 | 1 | Y0062077 |
02/10/2017 | 8/10/2017 | 1 | Y0062080 |
02/10/2017 | 8/10/2017 | 1 | Y0062099 |
02/10/2017 | 8/10/2017 | 1 | Y0062130 |
02/10/2017 | 8/10/2017 | 1 | Y0062183 |
02/10/2017 | 27/11/2017 | 1 | Y0061086 |
02/10/2017 | 27/11/2017 | 1 | Y0061518 |
02/10/2017 | 13/12/2017 | 1 | 05432727 |
02/10/2017 | 13/12/2017 | 1 | 05432730 |
02/10/2017 | 1/02/2018 | 1 | Y0062606 |
02/10/2017 | 1/02/2018 | 1 | Y0062638 |
02/10/2017 | 1/02/2018 | 1 | Y0062658 |
02/10/2017 | 1/02/2018 | 1 | Y0062790 |
02/10/2017 | 1/02/2018 | 1 | Y0062807 |
02/10/2017 | 1/02/2018 | 1 | Y0062839 |
02/10/2017 | 1/02/2018 | 1 | Y0062911 |
02/10/2017 | 1/02/2018 | 1 | Y0062919 |
My Vars are:
vStartExecutionWeek = Date([Execution Week])
vEndExecutionWeek = Date([Execution Week]+6)
This is the calculation to count the number of work between the Start and End of Execution week – which does not work
sum({<[Planned Finish Date]= {'>=$(vStartExecutionWeek)<=$(vEndExecutionWeek)'}>} Count)
The expected result will be 21 – data highlighted in yellow.
I have also try this variance:
sum({ Date= {'>=$(=vStartExecutionWeek)<=$(=vEndExecutionWeek) '}”}>} Count)
but this gives me all count which is not correct.
Thank you so much for your help.
Christian
