2 Replies Latest reply: Oct 18, 2017 7:53 AM by Sunny Talwar

# Set Analysis Date Range Variable

Hi all

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

• ###### Re: Set Analysis Date Range Variable

try this

count({<[Planned Finish Date]= {" >= \$(vStartExecutionWeek) <= \$(=vEndExecutionWeek)"}>} [Work Order])

try changing your variable to soemthing using max(field) or getfieldseletion(field) to get the date you are selecting so the above expression will work.

regards