Date count within month format problem

[#1]

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

[CELAMBARASAN]

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)

[avinashelite]

Try like this:

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

[Gysbert_Wassenaar · Accepted solution]

Try this:

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

[#5]

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?

[#6]

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!

[Gysbert_Wassenaar]

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.