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

    Set Analysis Date Range Variable

    Christian Lebe

      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