Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
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