topic Re: Stale bookmarks? in QlikView

Stale bookmarks?

Tue, 10 Jul 2012 17:31:30 GMT

I'm using a bookmark (BM08) where this expression has been entered into a search box:

=dev_finish_date>now()-3

Then I have a gauge chart that is using this expression:

=count({BM08} request_id)

The problem is that it pulls the correct figures for a while but as time goes by the number doesn't change. Is there an expression I can use that's dynamic?

Re: Stale bookmarks?

Tue, 10 Jul 2012 17:57:50 GMT

I'm looking into set analysis but still don't seem to be getting the right syntax for what I need:

Count({1<dev_finish_date={">now()-3"}>}request_id)

Count({< dev_finish_date = {">=$(=Date(now() - 3, 'M/D/YYYY'))"} > } request_id)

Neither of these are displaying any data, I don't even get a needle. Help?

Re: Stale bookmarks?

Tue, 10 Jul 2012 17:59:46 GMT

may be this:

Count({1<dev_finish_date={">date(now()-3)"}>}request_id)

Regards,

Kiran ROkkam.

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:14:25 GMT

No that doesn't work, either... If it's any help these are my set statements in case the date format is what's causing it:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:27:07 GMT

For further clarification, I'm trying to get the count of all the "request_id" records (a unique value) that have a "dev_finish_date" between the current time (now) and 72 hours/3 days ago.

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:29:19 GMT

The expression assumes that Qlikview recognies as dev_finish_date as a date field. You can check that tags for fields in table viewer. If its not date field format as date field in the script using date# function.

In the previous expression use timestamp() function instead of date. I didnt notice the timestamp part.

Kiran.

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:37:24 GMT

These didn't work either, with the date/time formats:

Count({1<dev_finish_date={">date(now()-3), M/D/YYYY"}>}request_id)

Count({1<dev_finish_date={">date(now()-3), M/D/YYYY, h:mm:ss TT"}>}request_id)

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:45:24 GMT

Can you try this (noticed some syntax issues):

Count({1<dev_finish_date={">$(=Timestamp(now()-3))"}>} request_id))

We need to try and match the dev_finish_date format to the return we have in the set analysis string.

Kiran Rokkam.

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:52:09 GMT

Getting closer - that at least gives me a value. But it is too high, quite a bit off. Ideas?

Re: Stale bookmarks?

Tue, 10 Jul 2012 18:59:32 GMT

I am not aware of the data but the following are possible reasons:

If you data with request_id in the future (> now) the expression evaluates them since there is upper limit
Notice using {1} in the set analysis. It ignores all the selections (expect for >now()-3) resulting in more values.

Kiran Rokkam.

Re: Stale bookmarks?

Tue, 10 Jul 2012 19:15:27 GMT

Much better. This expression is working for now:

Count({$<dev_finish_date={">$(=Timestamp(now()-3))"}>} request_id)

It's only a tad off now, and unless you have any other suggestions for the expression I'll have to wait and see if the server refresh rate is to blame for the discrpeancy.

EDIT:

I think it may be a server refresh issue, so this might be the most accurate they'll get. Thank you for all your time.

Message was edited by: bikeking8