Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having an issue creating an expression to display in a text box that will show me a count of IDs created on the current day, as well as for the last 4 weeks. I have tried using set analysis also and have not had any luck.
The date field that I am using is: CHECKIN_DT_TM and has a format of 'MM/DD/YYYY, 'HH:MM:SS.
I have tried multiple formulas with no success, any help/suggestions are welcome.
Thanks in advance.
Jordan
=count({<CHECKIN_DT_TM ={'$(=Today())'}>} ID)
or
=count({<CHECKIN_DT_TM ={'$(=date(floor(Today())))'}>} ID)
EDIT
=count({<CHECKIN_DT_TM ={">=$(=weekstart(today()-28))<=$(=date(floor(Today())))"}>} ID)
Hi
=count({<"CHECKIN_DT_TM>={'$(=weekstart(today()-28))'} and CHECKIN_DT_TM=<{'$(=date(floor(Today())))'}">}ID)
Hi Harshal,
Thank you for your response, however I keep getting the error ' Error in Expression: '}' expected', and the error line is on the '}' right before the 'and' in the expression.
I played around and attempted to add/move the ']' but had no success.
Thanks again,
Jordan
Hi Jordan,
I have tried what Massimo explained its perfectly working for me.
May be problem is in date column, try to exclude time stamp from the date.
You may get the solution.
Below is the excel file I used.
Regards,
Pavan
Hi jordan,
another approach:
=count({<CHECKIN_DT_TM={'>=$(=vweek)<=$(=vtoday)'}>}ID)
(variable)vweek: =weekstart(today()-28)
(variable)vtoday: =date(floor(Today()))
=count({<CHECKIN_DT_TM={">=$(=weekstart(today()-28))<=$(=date(floor(Today())))"}>}ID)
this is correct format specified by massimo if still you are getting trouble then you must check your CHECKIN_DT_TM date format cause if it not matches with expression it may result as 0 in your text-box result.
Hi, try like this
Create these variables
vPrev4Weeks = Date(weekstart(today()-28),'MM/DD/YYYY hh:mm:ss')
vCurrent = Date(Today(),'MM/DD/YYYY hh:mm:ss')
Use this expression
=Count({<CHECKIN_DT_TM={">=$(vPrev4Weeks)<=$(vCurrent)"}>}ID)