topic Re: Time intervalls with a defined time start time each day in QlikView

Time intervalls with a defined time start time each day

wardzynski — Wed, 22 Nov 2017 11:48:20 GMT

I need a column inside a straight table, that can based on the EntryTime of the trade, tell me the starting time of the candle bar where the trade occurs.

Example: 8 minute bars, start counting bars from 3:30 PM

Trade: Entry Time: Starting time of Bar:

1 4/4/2017 5:04:00 PM 4:58 PM

2 4/18/2017 8:16:06 PM 8:10 PM

My session starts at 3:30PM (Field value), and based on the time-frame (8 minutes, 15 minutes, etc, also field value) it should tell you the start-time of the Bar that the trade belongs to.

For 8 min time-frame: First bar starts at 3:30 PM, then next at 3:38 PM, 3:46 PM, 3:54 PM,etc...

For 10 min time-frame: First bar starts at 3:30 PM, next 3:40 PM, 3:50 PM, etc..

For 13 min time-frame: First bar starts at 3:30 PM

Time-frame value comes from Time-frame field (8 min, 9 min, etc...)

Session start time comes from session field (3:30 PM, 8:30 AM, etc...)

This is currently how I have tried scripting it inside the straight table chart:

=time(floor(EntryTimeII,8/24/60)+maketime(0,2,0),'hh:mm:ss TT')

But it is not correct.

Re: Time intervalls with a defined time start time each day

sunny_talwar — Wed, 22 Nov 2017 12:48:45 GMT

I am not sure I understand what you are trying to do here? Do you have a working sample you can share where we can dig deeper?

Re: Time intervalls with a defined time start time each day

wardzynski — Wed, 22 Nov 2017 13:45:23 GMT

Hi Sunny,

Imagine a starting time 3:30 PM, and that I want to split this in 8 min time-intervals, so we have abstract intervals starting with [3:30 PM, 3:38 PM, 3:46 PM, 3:54 PM, 4:02 PM, 4:10 PM, 4:18 PM, 4:26 PM: 4:34 PM, 4:42 PM, 4:50 PM, 4:58 PM, 5:06 PM, 5:14 PM, 5:22 PM, 5:30 PM, 5:38 PM, etc......]

Now, what I want is to see in which interval each of the timestamps (column: StartTime) belongs to showing in the column [WHAT I WANT]. The starting time (3:30 PM in this example) and time-frame (8 mins in this example) are defined in a variable or field).

How do I make the column "What I want" ?

StartTime	WHAT I WANT
3/1/2017 4:20:47 PM	04:18:00 PM
3/3/2017 3:54:00 PM	03:54:00 PM
3/3/2017 4:34:35 PM	04:34:00 PM
3/3/2017 5:28:21 PM	05:30:00 PM
3/3/2017 5:30:45 PM	05:30:00 PM
3/7/2017 4:13:04 PM	04:10:00 PM
3/7/2017 4:50:03 PM	04:50:00 PM
3/7/2017 5:02:31 PM	04:58:00 PM
3/7/2017 5:06:02 PM	05:06:00 PM
3/7/2017 5:17:09 PM	05:14:00 PM
3/7/2017 5:46:05 PM	05:46:00 PM
3/10/2017 4:02:17 PM	04:02:00 PM
3/10/2017 4:58:51 PM	04:58:00 PM
3/14/2017 4:09:01 PM	04:10:00 PM
3/14/2017 5:50:52 PM	05:46:00 PM
3/21/2017 4:31:01 PM	04:26:00 PM
3/21/2017 5:19:34 PM	05:14:00 PM
3/21/2017 5:35:36 PM	05:30:00 PM
3/22/2017 4:14:00 PM	04:10:00 PM
3/22/2017 4:31:17 PM	04:26:00 PM
3/22/2017 4:41:00 PM	04:42:00 PM
3/22/2017 4:54:32 PM	04:50:00 PM
3/22/2017 5:04:13 PM	05:06:00 PM
3/22/2017 5:12:10 PM	05:14:00 PM
3/22/2017 5:18:48 PM	05:14:00 PM
3/22/2017 5:30:35 PM	05:30:00 PM

Re: Time intervalls with a defined time start time each day

sunny_talwar — Wed, 22 Nov 2017 13:56:38 GMT

Try this

Table:

LOAD RowNo() as RowNum,

Time(MakeTime(15, 30) + Floor(Frac(StartTime - MakeTime(15, 29, 59)), 1/180)) as Time;

LOAD * INLINE [

StartTime

3/1/2017 4:20:47 PM

3/3/2017 3:54:00 PM

3/3/2017 4:34:35 PM

3/3/2017 5:28:21 PM

3/3/2017 5:30:45 PM

3/7/2017 4:13:04 PM

3/7/2017 4:50:03 PM

3/7/2017 5:02:31 PM

3/7/2017 5:06:02 PM

3/7/2017 5:17:09 PM

3/7/2017 5:46:05 PM

3/10/2017 4:02:17 PM

3/10/2017 4:58:51 PM

3/14/2017 4:09:01 PM

3/14/2017 5:50:52 PM

3/21/2017 4:31:01 PM

3/21/2017 5:19:34 PM

3/21/2017 5:35:36 PM

3/22/2017 4:14:00 PM

3/22/2017 4:31:17 PM

3/22/2017 4:41:00 PM

3/22/2017 4:54:32 PM

3/22/2017 5:04:13 PM

3/22/2017 5:12:10 PM

3/22/2017 5:18:48 PM

3/22/2017 5:30:35 PM

];

Re: Time intervalls with a defined time start time each day

sunny_talwar — Wed, 22 Nov 2017 13:58:33 GMT

Are you rounding down or rounding up or just rounding to 8 minutes?

Re: Time intervalls with a defined time start time each day

wardzynski — Wed, 22 Nov 2017 22:18:20 GMT

Hi Sunny,

I am rounding down to nearest starting point and I think it is working correct right now, still testing. The only problem is that I can not sort a second column now that includes the StartTime. The interactive sort wont show correct results.

My 2nd column uses the formula:

=time(StartTime,'mm:ss')-time([Start of Bar],'mm:ss')

[StartTime]	Start of Bar	2nd column = diff between [StartTime] and [Start of bar]

3/7/2017 9:40:18 PM	9:38:00 PM	02:18
3/9/2017 6:50:07 PM	6:50:00 PM	00:07
3/9/2017 5:30:23 PM	5:30:00 PM	00:23
3/9/2017 9:30:26 PM	9:30:00 PM	00:26
3/9/2017 4:51:25 PM	4:50:00 PM	01:25
3/9/2017 9:00:05 PM	8:58:00 PM	02:05
3/9/2017 5:00:06 PM	4:58:00 PM	02:06
3/9/2017 9:10:19 PM	9:06:00 PM	04:19
3/9/2017 7:02:24 PM	6:58:00 PM	04:24
3/10/2017 5:30:19 PM	5:30:00 PM	00:19
3/10/2017 6:50:44 PM	6:50:00 PM	00:44
3/10/2017 9:40:17 PM	9:38:00 PM	02:17
3/10/2017 7:32:33 PM	7:30:00 PM	02:33

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 03:07:08 GMT

Maybe I should try using the dual() function and sort it by expression?

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 10:45:31 GMT

I solved it using the frac() function.

=time(frac(StartTime),'mm:ss')-time(frac([Start of Bar]),'mm:ss')
Thank you for your help Sunny!

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 12:06:04 GMT

Sunny,

One last question, how do I change the formula dynamically if the timeframe is changed to 9 min, 13 min or 16 min ?

Re: Time intervalls with a defined time start time each day

MarcoWedel — Thu, 23 Nov 2017 22:18:49 GMT

Hi,

maybe also possible:

Time(Floor(Frac(StartTime),'00:08','15:30')) as [WHAT I WANT]

hope this helps

regards

Marco

Re: Time intervalls with a defined time start time each day

sunny_talwar — Thu, 23 Nov 2017 22:31:14 GMT

Is the timeframe decided before the reload, or is this something users will have the options to decide on the fly? If it is on the fly, then may be it makes sense to do all the calculation in the front end...

Your calculated dimension will look like this

=Time(Floor(StartTime) + Time(MakeTime(15, 30) + Floor(Frac(StartTime - MakeTime(15, 29, 59)), vTime/1440)))

Where vTime is determined by the selection you make in the input box's drop down....

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 23:16:39 GMT

Hi Sunny,

Yes, the time-frame will be changed in the front-end, so not only 8 min is applicable.

Also, the starting time needs to be changed in the front-end.

Currently, these come from Field-values (8min, 9min, 10min, etc..) and start-time (3:30 PM or 9:30 PM, etc...)

Just so I dont confuse:

There is a start time of the day where the bars are starting from, like 3:30 PM or 9:30 PM, and there are the particular starting times for each trade in the column StartTime.

Does Maketime(15,30) together with Maketime(15,29,59) indicate the 3:30 PM initial starting time?

Re: Time intervalls with a defined time start time each day

sunny_talwar — Thu, 23 Nov 2017 23:19:13 GMT

So, I would suggest using the above mentioned technique... did you check it yet?

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 23:24:50 GMT

Hi Marco,

This is also working.

So, all I need is to change the '00:08' to select a different time-frame and 15:30 to select a different initial starting time?

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 23:29:14 GMT

What I have right now:

Re: Time intervalls with a defined time start time each day

sunny_talwar — Thu, 23 Nov 2017 23:34:49 GMT

Does Maketime(15,30) together with Maketime(15,29,59) indicate the 3:30 PM initial starting time?

That is right

Re: Time intervalls with a defined time start time each day

sunny_talwar — Thu, 23 Nov 2017 23:35:01 GMT

So what in here would like to change?

Re: Time intervalls with a defined time start time each day

MarcoWedel — Thu, 23 Nov 2017 23:39:36 GMT

Yes, that would be all.

On the other hand, I noticed some rounding issues in this solution that might lead to unexpected behaviour, e.g. 3:54:00 PM being rounded off to 3:46:00 PM.

hope this helps nevertheless

Marco

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 23:48:10 GMT

So if I get my initial starting time (3:30 PM) from a Field, then I would need to split it into mins and sec, and also mm:ss - 1 sec or such?

Re: Time intervalls with a defined time start time each day

wardzynski — Thu, 23 Nov 2017 23:48:43 GMT

Thats unfortunate, as your solution seemed so simple. No rounding errors are acceptable here