topic Re: Count items after a certain date field in QlikView

Count items after a certain date field

Tue, 03 Jun 2014 04:11:58 GMT

Hi,

I'm trying to count the number of deliverables that is greater than a certain date field. As you can see from below, my expression is counting 11, when it should really be only counting 5 entries greater than 1/7/2013.

Thanks for any help.

Cheers.

Re: Count items after a certain date field

Anonymous — Tue, 03 Jun 2014 04:36:19 GMT

Hello, Carlo.

Try adding single cotes around your $-sign substitution of the variable, like this:

Count({<DeliverDate = {">='$(CurrentEntitlementeStartDate)'"}>} Distinct Account & CSP_Name)

I hope it helps.

Cheers!

Re: Count items after a certain date field

tresB — Tue, 03 Jun 2014 04:45:14 GMT

I guess CurrentEntitlementStarDate is a field, if so, try like:

=Count({<DeliveryDate={">=$(=CurrentEntitlementStarDate)"}>}) Distinct ....)

Re: Count items after a certain date field

MayilVahanan — Tue, 03 Jun 2014 05:18:04 GMT

Both the date format is not same. So you need to change it.

In script, do like this

Load Date(DeliveryDate, 'DD/MM/YYYY') as DeliveryDate, ..

...

from tablename;

Then

=Count({<DeliveryDate={">=$(=CurrentEntitlementStarDate)"}>}) Distinct Account&CSP_Name)

Re: Count items after a certain date field

Tue, 03 Jun 2014 06:06:48 GMT

Thanks for this - i changed the date format and it is working.

However, i've found something strange where this will work if i have my dashboard filtered down to 1 account. If I clear my selections to expand the accounts - the expression no longer displays data. If I remove the = (equal sign) as you suggested, the data appears but it reverts back to its inaccurate state listing items that are not "greater than" the date field. What does the 'equals' sign do?

Any ideas of why this behaviour occurs?

Thanks.