Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

Date function on Field not working on a count

I have a Count which is giving me the following error:

Error in set modifier expression

The Expression is:

=Count({<date(STARTTIMEDATE)={"$(=Date(Today()))"}>} IDID)

STARTTIMEDATE is in the format of YYYY-MM-DD HH:MM:SS:XXXXXX

Labels (1)
9 Replies
sunny_talwar

May be this

=Count({<STARTTIMEDATE = {">=$(=TimeStamp(Today(), 'YYYY-MM-DD hh:mm:ss:fff))<=$(=TimeStamp(DayEnd(Today()), 'YYYY-MM-DD hh:mm:ss:fff))"}>} IDID)

or change the field to a date field in the script like this

Date(Floor(STARTTIMEDATE)) as STARTDATE

and then this

=Count({<STARTDATE = {"$(=Date(Today()))"}>} IDID)

 

matthewp
Creator III
Creator III
Author

=Count({<STARTTIMEDATE = {">=$(=TimeStamp(Today(), 'YYYY-MM-DD hh:mm:ss:fff))<=$(=TimeStamp(DayEnd(Today()), 'YYYY-MM-DD hh:mm:ss:fff))"}>} IDID)

 

Is returning the same as if i was just doing =COUNT(IDID)

sunny_talwar

Would you be able to post a screenshot of STARTTIMEDATE field in a filter box in your Qlik Sense dashboard?

matthewp
Creator III
Creator III
Author

Here are some examples of STARTTIMEDATE :

 

2018-01-02 06:11:19.617
2018-01-02 06:11:40.260
2018-01-02 06:12:17.260
2018-01-02 06:12:38.740
2018-01-02 06:12:52.403

sunny_talwar

That is what you see in Qlik Sense or the source?

matthewp
Creator III
Creator III
Author

Both,

 

I also created a new field that is just dd/mm/yyyy and tried =Count({<STARTDATE= {"$(=Date(Today()))"}>} IDID) but get no results

sunny_talwar

Can you share the script you used to create the new field?

matthewp
Creator III
Creator III
Author

I have resolved this by using  this in my load script:

CONVERT(VARCHAR(10),CAST(STARTTIMEDATE as DATE),103) AS STARTDATE

Then this as my expression:

=Count({$<YEAR=,STARTDATE={"$(=Date(Today()-1))"}>} IDID)

sunny_talwar

Do you have a preceding load on top of your query? If you do, add this in there

LOAD Date(STARTDATE) as STARTDATE,
     ...,
SQL SELECT ...

see if that works for you