topic Re: Variable for past 24 hours in QlikView

Variable for past 24 hours

Anonymous — Wed, 13 May 2015 09:07:11 GMT

Hi Friends,

I am using the below script for creating a variable for Previous day's date.

LET varPreviousDay = '(' & DayName(DayStart(today()-1)) & ')';

Please someone help me with script for creating a variable - Past 24 Hours.

Re: Variable for past 24 hours

Gysbert_Wassenaar — Wed, 13 May 2015 09:12:35 GMT

LET v24HoursAgo = now()-1;

Re: Variable for past 24 hours

tresB — Wed, 13 May 2015 09:13:49 GMT

May a bit simpler:

LET varPreviousDay = '(' & DayName(today()-1) & ')';

Re: Variable for past 24 hours

Wed, 13 May 2015 09:15:49 GMT

Not as cleans as Gysberts but the same principle

LET v24H = date(now()-1)&' '& time(now())

Re: Variable for past 24 hours

simospa — Wed, 13 May 2015 09:19:28 GMT

My opinion too :

LET v24HoursAgo = Date(now()-1, 'MM/DD/YYYY hh:mm:ss');

if you want to see the timestamp...

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 09:26:45 GMT

Thanks for the prompt replies friends.

I have a column of TimeStamps like below.

5/12/2015 1:20:02 PM

5/11/2015 11:30:05 PM

etc.,

I am trying to select dates (timestamps) for the tickets created in last 24 hours.

I tried LET varPreviousDay = '(' & DayName(DayStart(today()-1)) & ')'; and the result is it is selecting 5/12/2015 tickets.

But I want to select the last 24 hours. The current time of mine is now 5/13/2015 2:55 PM IST.

I want to select the date / time stamps for last 24 hours like 5/12/2015 2:55 PM IST to 5/13/2015 2:55 PM IST.

Re: Variable for past 24 hours

Wed, 13 May 2015 09:32:38 GMT

Hi,

Setup an expression where you only show the values that are bigger then your set variable time stamp and you should get the correct behavior.

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 09:37:39 GMT

Hampus, Sorry for asking. I am not able to understand. I have tried the last response received LET v24HoursAgo = Date(now()-1, 'MM/DD/YYYY hh:mm:ss'); and it is showing the variable result as time stamp before 24 hours. But when I am using this variable in a button. it is looking for the entry at that particular timestamp.

We need to find a way for a script variable to show the time stamp which will select all the timestamp entries for the last 24 hours.

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 09:40:43 GMT

to be simpler... Select all the tickets created between the timestamp of last 24 hours.

Re: Variable for past 24 hours

simospa — Wed, 13 May 2015 09:54:44 GMT

What about create a field concatenated as yyyymmddhhmmss, either for variable and your timestamps and use that?

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 10:13:09 GMT

How do you suggest creating the field please..

Can you please let me know if we can create a variable like below.

Between DD/MM/YYYY HH:MM:SS and DD/MM/YYYY HH:MM:SS

A variable for timestamp which comes in between last 24 hours

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 10:56:03 GMT

Any suggestions for selecting dates after - 10 hours

Re: Variable for past 24 hours

simospa — Wed, 13 May 2015 11:49:13 GMT

You could use Num function. For example this statement

LET v24HoursAgo = Num(Date(now()-1, 'MM/DD/YYYY hh:mm:ss')); //number that identify the timestamp of 24 hours ago in numeric format

LET v10HoursAgo = timestamp(Now() - 10/24); //10 hours ago

LET v10HoursAgoNumeric = Num(timestamp(Now() - 10/24)); //10 hours ago in numeric format

creates a variable like 42136,571666667 at 05/12/2015 13:43:12, 42136,571840278 at 05/12/2015 13:43:27, and so.

I don't know where yout timestamp values come from, but with a Load you can create an additional field that convert your timestamp in format viewed (something like Num(myTimeStamp as timeStampNum). At this point you can use it as numbers.

Let us know.

Re: Variable for past 24 hours

simospa — Wed, 13 May 2015 11:51:22 GMT

LET v10HoursAgo = timestamp(Now() - 10/24); //10 hours ago

Re: Variable for past 24 hours

Anonymous — Wed, 13 May 2015 12:12:12 GMT

Thanks Simone.

My TimeStamp field is CreatedDate.

When I am using this Variable LET v10HoursAgo = timestamp(Now() - 10/24); //10 hours ago I am able to get the result but....

When I am using this via Select In Field using a button to select the v10HoursAgo TimeStamp it is selecting the ticket created on the particular time which is 10 hours ago.

For Example: If the v10HoursAgo result is 05/12/2015 13:43:27 then when I am using this selection it displays the tickets created on 05/12/2015 13:43:27 and if there is no tickets created on 05/12/2015 13:43:27 then my selection result is 0. I mean if a ticket is created on 05/12/2015 13:45:00 instead of 05/12/2015 13:43:27 then when I do a selection using v10HoursAgo the selection result is 0.

Our ultimate goal is to display all the ticket numbers created since the last 10 hours.... Need your valuable helps.

Re: Variable for past 24 hours

simospa — Wed, 13 May 2015 12:44:41 GMT

The problem now seems to be in Select in field.

Type in Search String field something like this

='>= $(=v10HoursAgo)'

Let me know.