Count - if - date in chart expression for rolling week?

[#1]

I have two fields, dev_finish_date and request_id.

I need to create a gauge chart counting all the request_id's with a dev_finish_date occurring in the past seven days (rolling). Another field, app_ id has to be included in the calculation, too, as a required selection. I've racked my brain and the best syntax I can come up with for the gauge chart's expression is this:

count({<dev_finish_date={[>now-7]}, app_id={'22'}>} request_id

but that obviously isn't working.

Any ideas?

[swuehl]

Try

count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id

[#3]

I get "No data to display" for some reason. As long as it can count the number of records in the "request_id" field, I'm fine with that. I can just apply a bookmark upon sheet load for the "app_id" field. This doesn't work, though:

count({<dev_finish_date={'Today-7'}>} request_id)

or this:

count({<dev_finish_date={">$(=date(today()-7))"}>} request_id

...and I still have three more date calculations to put into graph form by Tuesday. After someone helps me sort this out, can anyone tell me what language Qlikview is written in so I can pick up a reference book?

[swuehl]

I assume it's just a format issue. How is your dev_finish_date formatted? and what is your default DateFormat (set in the script)?

You could also give it a try to remove the date() function from the set expression search expression:

count({<dev_finish_date={">$(today()-7)"}, app_id={'22'}>} request_id

[#5]

Through the use of bookmarks (again), I found a way to permanently detach the results of my gauge chart. I made a bookmark with the required search conditions via a search box:

=dev_finish_date>now()-7

...along with the app_id "22" selected. For the chart I used this expression:

=Count({BM08} request_id)

Done. Now, no matter what bookmarks or records the user clicks on, the desired calculation will show. Thanks for all the help.

[#6]

For some reason, the bookmarked search is "stale" every time I load/reload the QVD, For example, when I loaded it today and checked the results of the bookmarked search, it pulled up dates from 2/21-2/23 as opposed to 2/26-2/24. The expression I'm using is:

=Count({BM08} request_id)

...where the bookmark is a result of the search where app_id "22" is manually selected and the following string is entered into the search box:

=dev_finish_date>now()-3

The criteria has changed to the past 3 days, hence the 3. I've also tried "today" in place of "now", but the same thing happens. I have the document reload on open via module operation, so it shouldn't be a data lag issue. In answer to the above question concerning date format, the dates are formatted as 2/26/2012 10:41:34 AM; using a 24 hour clock (13:00, 14:00, etc).

The two identical expressions that were posted to try out:

count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id

only give me a "no data to display" on the gauge chart. I see there's a parantheses missing, but I can't figure out where to put it. Please help?

[#7]

Just a thought; would the bookmarked search behave as required if there was a dollar sign somewhere in there? If so, what would be the correct search string?

Just tried this as an expression, as well:

=count({<dev_finish_date={">$(=date(today()-3))"},app_id={'22'}>} request_id)

but this gave me a result of 0 on the gauge chart.

[swuehl · Accepted solution]

The missing parenthesis is needed at the very end of the line, closing the count() function arguments.

=count({<dev_finish_date={">$(=date(today()-7))"}, app_id={'22'}>} request_id)

Be careful using now() function, the standard timer mode here is 1, so you will constanty update the time and function (could slow down the system). Check the different timer modes in the Help for detail.

I am not sure what you mean with the bookmarked search and the dollar sign, could you post your expression here?

Your dev_finish dates are actually timestamps, so just make sure to format the search expression accordingly, try  these:

count({<dev_finish_date={">$(=timestamp(today()-7),'M/DD/YYYY hh:mm:ss TT')"}, app_id={'22'}>} request_id)

count({<dev_finish_date={">$(=timestamp(today()-7))"}, app_id={'22'}>} request_id)

count({<dev_finish_date={">$(=today()-7)"}, app_id={'22'}>} request_id)

Have you tried the expressions also in a text box? Just had a thought that it might be a gauge chart issue.

Could you upload a small sample file here to the forum?

Regards,

Stefan

[#9]

Thanks. The suggestion you provided:

count({<dev_finish_date={">$(=timestamp(today()-7))"}, app_id={'22'}>} request_id)

works for now when used as the expression in the gauge chart. I'll see if it's consistent come tomorrow afternoon. That's the problem with working with date fields, I've discovered; you might have to wait a day or two to get another "data sample" when it comes to consistency checks.