topic Count Records Less than Date Selection in Listbox in QlikView

Count Records Less than Date Selection in Listbox

Wed, 08 Apr 2015 14:03:46 GMT

I've created a test file where I am attempting to count records less than or equal to a variable date value. When a selection is made in the YearQuarter listbox, I want the count to include all records <= the variable vFPYQuarterEnd.

In the example, if you select 2014-3, the variable vFPYQuarterEnd = 9/30/2014 and all records less than or equal to this date should be counted, including those that do not fall within the the actual quarter. I would expect to see a count of 12 records. Instead, I am receiving a count of 9 records which represents on the records in the 2014-3.

Re: Count Records Less than Date Selection in Listbox

pokassov — Wed, 08 Apr 2015 14:13:49 GMT

Hi!

You have to exclude YearQuarter from filter like this:

='# IDs <= Max Quarter End Date = ' & COUNT({$<

YearQuarter=,

MASTERDATE={"<=$(=$(vFPYQuarterEnd))"}

>} ID_NUM)

Sergey

Re: Count Records Less than Date Selection in Listbox

sunny_talwar — Wed, 08 Apr 2015 14:15:27 GMT

The issue is that when you select a yearQuarter it restrict your date to that particular quarter itself. If you want to ignore selection in YearQuarter you can use your expression like this instead:

=COUNT({$<MASTERDATE={"<=$(=$(vFPYQuarterEnd))"}, YearQuarter = >} ID_NUM)

HTH

Best,

Sunny

Re: Count Records Less than Date Selection in Listbox

sunny_talwar — Wed, 08 Apr 2015 14:16:47 GMT

Attaching your qvw for reference

Best,

Sunny

Re: Count Records Less than Date Selection in Listbox

Wed, 08 Apr 2015 14:41:31 GMT

Thank you for the info from both of you. It's working exactly as I wanted now.

Re: Count Records Less than Date Selection in Listbox

sunny_talwar — Wed, 08 Apr 2015 14:42:18 GMT

Glad to help.

Best,

Sunny