Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
christianlebe
Contributor
Contributor

Set Analysis Date Range Variable

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

2 Replies
pradosh_thakur
Master II
Master II

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

Learning never stops.
sunny_talwar

Try this

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