topic Re: Count All IDs for Current Day in QlikView

Count All IDs for Current Day

Fri, 04 Apr 2014 19:59:53 GMT

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

Re: Count All IDs for Current Day

maxgro — Fri, 04 Apr 2014 21:00:26 GMT

=count({<CHECKIN_DT_TM ={'$(=Today())'}>} ID)

=count({<CHECKIN_DT_TM ={'$(=date(floor(Today())))'}>} ID)

EDIT

=count({<CHECKIN_DT_TM ={">=$(=weekstart(today()-28))<=$(=date(floor(Today())))"}>} ID)

Re: Count All IDs for Current Day

Sat, 05 Apr 2014 06:04:49 GMT

=count({<"CHECKIN_DT_TM>={'$(=weekstart(today()-28))'} and CHECKIN_DT_TM=<{'$(=date(floor(Today())))'}">}ID)

Re: Count All IDs for Current Day

Sat, 05 Apr 2014 17:10:35 GMT

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

Re: Count All IDs for Current Day

Sun, 06 Apr 2014 00:00:30 GMT

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

Re: Count All IDs for Current Day

Mon, 07 Apr 2014 06:34:54 GMT

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.

Re: Count All IDs for Current Day

Mon, 07 Apr 2014 06:48:09 GMT

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)