topic Re: Displaying data between two time ranges in QlikView

Displaying data between two time ranges

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I have data available in a similar timestamp format : 11/2/2016 11:28:00 AM.

I have a requirement wherein I need to display records/data between a particular time range ie. 9:00 AM to 5:00 PM using the above timestamp.

Could you please with this query.

Re: Displaying data between two time ranges

Anil_Babu_Samineni — Tue, 09 Jan 2018 13:14:42 GMT

May be this?

Load TimeStamp From Table

Where Date(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm TT') >= '09:00 AM' and Date(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm TT') <= '05:00 PM';

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 04:53:38 GMT

Hi Anil, thank you for your reply. But it doesn't seem to load any data in spite of the data being present.

Re: Displaying data between two time ranges

jonathandienst — Wed, 10 Jan 2018 05:02:02 GMT

Is your date in DMY or MDY format? Do you want this in the load script or as an expression?

The condition should be like:

Where Frac(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT')) > 9/24 And Frac(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT')) <= 17/24

9/24 and 17/24 represent the time limits as a fraction of a day.

You could simplify this by converting the timestamp string values to QV time/date values during the initial load.

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 05:04:24 GMT

The date is in the following format :

M/D/YYYY h:mm:ss[.fff] TT

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 05:07:42 GMT

That condition isn't working either. I am trying it in the load at the moment. Expression would work too.

Re: Displaying data between two time ranges

Anil_Babu_Samineni — Wed, 10 Jan 2018 05:27:52 GMT

May be share sample data set, I've tested my sample. Looks working.. Can you work using below?

Date(Date#(TimeStamp, 'M/D/YYYY h:mm:ss[.fff] TT'), 'hh:mm TT') >= '09:00 AM' and Date(Date#(TimeStamp, 'M/D/YYYY h:mm:ss[.fff] TT'), 'hh:mm TT') <= '05:00 PM';

Note:- If none of the working, We would request you to share sample excel file that demon the issue.

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 05:40:21 GMT

Hi Anil, please find attached the qvw file. [Creation Date] is the timestamp I was referring too.

Re: Displaying data between two time ranges

Anil_Babu_Samineni — Wed, 10 Jan 2018 05:42:18 GMT

Don't have access to open the app due to live project. So, will this possible to share excel file of data? So, Then i can test excel itself and will convert to Qlik

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 05:49:05 GMT

No problem Anil. Attached is the base data

Re: Displaying data between two time ranges

Anil_Babu_Samineni — Wed, 10 Jan 2018 05:55:20 GMT

Try to use this in list box and show the image

Interval(Date#([Creation Date], 'MM/D/YYYY hh:mm:ss TT') - Date#(Timezone, 'hh:mm:ss TT'), 'hh:mm')

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 06:02:43 GMT

Doesn't work.

Re: Displaying data between two time ranges

mdmukramali — Wed, 10 Jan 2018 06:06:22 GMT

Hi,

Can you try this.

where Time(frac([Creation Date]))>=MakeTime(9,0,0) and Time(frac([Creation Date]))<=MakeTime(17,0,0);

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 06:11:54 GMT

Hi Mohammed, Perfect. It worked. Thank you. Could you explain how it works?

Re: Displaying data between two time ranges

mdmukramali — Wed, 10 Jan 2018 06:22:45 GMT

Hi,

From [Creation Date] first took Fraction Part which is Time then Applied Time Format to it.

and I have used MakeTime Function to define the Time 9 Am and 5 PM.

then compared both Time Values.

PFA for reference.

Thanks,

Mukram.

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 06:25:16 GMT

Oh great, got it. Thank you so much. Quick question, if I wish to display the data between 5 PM and 1 AM, would the expression look like this ? where Time(frac([Creation Date]))>=MakeTime(17,0,0) and Time(frac([Creation Date]))<=MakeTime(1,0,0);

Re: Displaying data between two time ranges

mdmukramali — Wed, 10 Jan 2018 06:47:51 GMT

Hi,

you can try this.

Where Time(frac([Creation Date]))<=MakeTime(1,0,0) or Time(frac([Creation Date]))>=MakeTime(17,0,0);

Re: Displaying data between two time ranges

mdmukramali — Wed, 10 Jan 2018 08:38:00 GMT

Hi Joshua,

joshpala‌

Do you have any further questions?

If not, then please close this thread by marking correct and helpful answers.

so it will helpful for others who are looking for similar solution.

Thanks,

Mukram.

Re: Displaying data between two time ranges

Anonymous — Wed, 10 Jan 2018 08:59:23 GMT

Thank you for helping out Mohammed

Re: Displaying data between two time ranges

Tanya_Sharma9 — Thu, 20 May 2021 10:14:58 GMT

Hi Mukram,

I have the same query, I need to use an expression in a line chart to display data for one hour. I have a 'TimeStamp' column which is on my X Axis(Dimension) , but when I used your expression in my 'Field' section, it didn't work. Could you please help me resolve the same ?