Set Analysis Date Range Variable

christianlebe — Wed, 18 Oct 2017 01:53:27 GMT

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

Re: Set Analysis Date Range Variable

pradosh_thakur — Wed, 18 Oct 2017 07:50:01 GMT

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

Pradosh

Re: Set Analysis Date Range Variable

sunny_talwar — Wed, 18 Oct 2017 11:53:35 GMT

Try this

Sum({<[Planned Finish Date]= {">=$(=vStartExecutionWeek)<=$(=vEndExecutionWeek)"}>} Count)

topic Re: Set Analysis Date Range Variable in App Development

Set Analysis Date Range Variable

Re: Set Analysis Date Range Variable

Re: Set Analysis Date Range Variable