topic Date count within month format problem in QlikView

Date count within month format problem

Tue, 04 Nov 2014 09:34:33 GMT

Hello guys,

I'm having trouble figuring this out, and I can not find the entire format for dates.

So I have dates in the following format:

2014-09-16 15:32:05.6792259 +02:00

And I'm trying to count the number of fault occurences within a month using the following function:

=count({(< CreatedAt<{'2014-02-01 00:00:00.0000000 +02:00'}&& CreatedAt>{'2014-01-01 00:00:00.0000000 +02:00'} >)}FaultId)

Where column CreatedAt contains the date. This examples should count all dates in January, as the date has to be greater than January 1st and smaller than February 1st.

However, this doesn't work at all and I'm having a hard time finding the right format for this.

What format should I use?

Thanks in advance,

Sijmen

Re: Date count within month format problem

CELAMBARASAN — Tue, 04 Nov 2014 09:39:00 GMT

Try with Set analysis like this

Count({< CreatedAt={"<'2014-02-01 00:00:00.0000000 +02:00'>'2014-01-01 00:00:00.0000000 +02:00'"} >}FaultId)

Re: Date count within month format problem

avinashelite — Tue, 04 Nov 2014 09:39:25 GMT

Try like this:

=count({<CreatedAt={">=$(=('2014-02-01 00:00:00.0000000 +02:00''))<=$(=(2014-01-01 00:00:00.0000000 +02:00'))"}>}FaultId)

Re: Date count within month format problem

Gysbert_Wassenaar — Tue, 04 Nov 2014 09:45:03 GMT

Try this:

count({<CreatedAt={'>=$(=num(makedate(2014,1)))<$(=num(makedate(2014,2)))'}>}FaultId)

Re: Date count within month format problem

Tue, 04 Nov 2014 09:51:24 GMT

Hey, thanks for your quick response.

Somehow your function returns only 0's, also I don't quite understand how your function works. Could you elaborate?

Re: Date count within month format problem

Tue, 04 Nov 2014 09:53:55 GMT

Your function returns only 0's as well. I believe CreatedAt is not recognized as a date, but rather as a string. Does this prevent the dates from being compared properly?

EDIT: that appeared to be the problem. I changed DateFormat to 'DateFormat='YYYY-MM-DD hh:mm:ss.fffffff +02:00' in the load script, and that solved the problem.

Thank you very much!

Re: Date count within month format problem

Gysbert_Wassenaar — Tue, 04 Nov 2014 10:12:51 GMT

If CreatedAt contains strings values instead of timestamps then you can only do text comparisons. You can use the timestamp# or date# function in the script to create a timestamp or date value from a string.