Set analysis - field > a date format

[william_allen]

Hi All,

I am having an issue with the following expression hopefully someone can help shed a little light on it.

=count({1<[CALL URGENCY]={'Critical'}, [CALL STATE]={'Open'}, [ACTUAL_LOG_DATE]={"<$(vCriticalTime)"}>} [CALL_NUMBER])

The problem is with:  [ACTUAL_LOG_DATE]={"<$(vCriticalTime"}

To explain what's going on a little better:

vCriticalTime = Timestamp(now()-0.1667, 'YYYY-MM-DD hh:mm:ss')

ie vCriticalTime = '2014-07-23 10:46:37 '

The count always comes out as 0 which is wrong.

Can anyone see where I'm going wrong when trying to determine if my vCriticalTime is less than the [ACTUAL_LOG_DATE]?

Thanks for your help

Update fixed the missing ) , still having same issue

[annafuksa1]

you are worry about data ? just leave in app like 10 records of data and one graph that you want to work 

if you do not know how :

you need to click at: 'Use advanced editor' and there you can find option 'Attach'

[#3]

Are you looking to show records that have a [ACTUAL_LOG_DATE] past the current time, is it possible that you don't have such records?

[marcus_malinow]

Try this:

[ACTUAL LOG DATE]={"<$(=vCriticalTime)"}

Note the extra Equals sign

[william_allen]

The records are there I have checked in the DB, at the minute there are 7 records.

The critical time is current time - 4 hours.

Thanks

[william_allen]

Thanks Marcus, I tried it no joy

[annafuksa1]

Please see my file. I have use Qlikview example data and its working

[william_allen]

Thanks for taking the time to do that Anna.

The difference between yours and mine seems to be the format of the date.

Your vCriticalTime = Timestamp#(now()-0.1667)

Mine vCriticalTime = Timestamp(now()-0.1667, 'YYYY-MM-DD hh:mm:ss')

Maybe I should convert both Actual Log Date and Critical Time to be numerical.

[marcus_malinow]

Hi William,

It may be that yours is being evaluated as a string rather  than a timestamp. That's the fundamental difference between the timestamp and timestamp# functions.

Marcus

[william_allen]

OK, so I have tried converting to numerical for ACTUAL_LOG_DATE when loading it to the table:

Num(Date#(ACTUAL_LOG_DATE, 'YYYY-MM-DD hh:mm:ss')) AS ACTUAL_LOG_DATE,

And also converted my vCriticalTime to be numerical like so:

=Timestamp#(now()-0.1667, 'YYYY-MM-DD hh:mm:ss')

But it's still not taking into consideration this part:

[ACTUAL_LOG_DATE]={"<$(vCriticalTime)"}

I'm getting the same result with:

=count({1<[CALL URGENCY]={'Critical'}, [CALL STATE]={'Open'}, [ACTUAL_LOG_DATE]={"<$(vCriticalTime)"}>} [CALL_NUMBER])

AS

=count({1<[CALL URGENCY]={'Critical'}, [CALL STATE]={'Open'}>} [CALL_NUMBER])

Which isn't correct.

I hope that makes sense.

Thanks

[annafuksa1]

another difference is here

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff] TT';

please look at my code I comment standard version and put my version