topic Re: Set analysis - field > a date format in QlikView

Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 13:52:10 GMT

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

Re: Set analysis - field > a date format

Wed, 23 Jul 2014 13:54:23 GMT

Looks like you might have forgot a closing parenthesis:

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

Re: Set analysis - field > a date format

preminqlik — Wed, 23 Jul 2014 13:56:58 GMT

hi try this

you didnot closed the variable bracket try below one

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

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 14:03:08 GMT

Thanks I noticed that after I pasted it up, still doesn't fix it. The count is coming back as 0

Re: Set analysis - field > a date format

lironbaram — Wed, 23 Jul 2014 14:07:55 GMT

you need to make sure

that both vCriticalTime and Actual_Log_Date have the same format

this is a reason why for those kind of formula i like to have my date fields as numbers

Re: Set analysis - field > a date format

Wed, 23 Jul 2014 14:09:57 GMT

Are you able to share a sample?

Re: Set analysis - field > a date format

Wed, 23 Jul 2014 14:10:51 GMT

A few suggestions:

Instead of the variable try hard-coding the value and see if the expression works fine
If it does, then next put the variable in a text box and see if you get the desired result
Probably while defining the variable in Settings > Variable overview you might have to use '=' then the expression

Give it a whirl. If none of these work please attach a sample file for deeper investigation.

Thanks

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 14:11:13 GMT

Thanks Liron, I checked, they do have the same format here are examples of different values for them

Actual log date = 2014-07-22 10:40:23.000

Critical Time = 2014-07-23 11:08:47

Re: Set analysis - field > a date format

annafuksa1 — Wed, 23 Jul 2014 14:24:11 GMT

Could you post your App ? it will be faster

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 14:34:46 GMT

Sorry I can't post a version of the app.

Re: Re: Set analysis - field > a date format

annafuksa1 — Wed, 23 Jul 2014 14:41:45 GMT

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'

Re: Set analysis - field > a date format

Wed, 23 Jul 2014 14:44:31 GMT

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?

Re: Set analysis - field > a date format

marcus_malinow — Wed, 23 Jul 2014 14:46:19 GMT

Try this:

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

Note the extra Equals sign

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 14:55:26 GMT

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

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 14:55:53 GMT

Thanks Marcus, I tried it no joy

Re: Re: Set analysis - field > a date format

annafuksa1 — Wed, 23 Jul 2014 15:03:51 GMT

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

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 15:12:06 GMT

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.

Re: Set analysis - field > a date format

marcus_malinow — Wed, 23 Jul 2014 15:21:03 GMT

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

Re: Set analysis - field > a date format

william_allen — Wed, 23 Jul 2014 16:18:56 GMT

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])

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

Which isn't correct.

I hope that makes sense.

Thanks

Re: Set analysis - field > a date format

annafuksa1 — Wed, 23 Jul 2014 17:25:05 GMT

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