topic Re: Count between 2 dates in QlikView

Count between 2 dates

Tue, 13 May 2014 08:40:03 GMT

I am trying to count the number of CATEGORY=EM calls we have between 2 dates.

I am able to use set analysis to derive the dates, which I've put in separate columns in my chart for debug purposes. These are as follows:

To get the latest date:

only({<METER_NO={'1','METER1','Black','BLACK','A4BLACK'}, READINGS_READING_DETAILS_Z_ASSOC_ROW={'1'}>}DATE_READ)

To get the 4th old reading:

only({<METER_NO={'1','METER1','Black','BLACK','A4BLACK'}, READINGS_READING_DETAILS_Z_ASSOC_ROW={'4'}>}DATE_READ)

This is where it becomes a little too complex for me. I know this is a very dumbed down example of how to count between 2 dates, but I somehow need to stitch the 2 pieces of set analysis in.

count({<DATE_READ={"$(= '>=' & '01/01/2010' & '<=' & '31/12/2012')"}>} distinct SERIAL)

Any help would be gratefully received.

Re: Count between 2 dates

SunilChauhan — Tue, 13 May 2014 08:51:37 GMT

count({<DATE_READ={">=$(=datefield1) <= $(=datefield2)"}>} distinct SERIAL)

count({<DATE_READ={">=$(='01/01/2010') <= $(='31/12/2012)"}>} distinct SERIAL)

hop ethis helps

Re: Count between 2 dates

Tue, 13 May 2014 08:55:51 GMT

Do you mean create variables for the 2 date selections?

Re: Count between 2 dates

SunilChauhan — Tue, 13 May 2014 08:58:39 GMT

yes

you can use getfieldselections() function