Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

wardzynski
New Contributor III

Time intervalls with a defined time start time each day

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.

1 Solution

Accepted Solutions

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

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

];


Capture.PNG

20 Replies

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

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?

wardzynski
New Contributor III

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

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" ?

 

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

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

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

];


Capture.PNG

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

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

wardzynski
New Contributor III

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

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 Bar2nd column = diff between [StartTime] and [Start of bar]
3/7/2017 9:40:18 PM9:38:00 PM02:18
3/9/2017 6:50:07 PM6:50:00 PM00:07
3/9/2017 5:30:23 PM5:30:00 PM00:23
3/9/2017 9:30:26 PM9:30:00 PM00:26
3/9/2017 4:51:25 PM4:50:00 PM01:25
3/9/2017 9:00:05 PM8:58:00 PM02:05
3/9/2017 5:00:06 PM4:58:00 PM02:06
3/9/2017 9:10:19 PM9:06:00 PM04:19
3/9/2017 7:02:24 PM6:58:00 PM04:24
3/10/2017 5:30:19 PM5:30:00 PM00:19
3/10/2017 6:50:44 PM6:50:00 PM00:44
3/10/2017 9:40:17 PM9:38:00 PM02:17
3/10/2017 7:32:33 PM7:30:00 PM02:33
wardzynski
New Contributor III

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

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

wardzynski
New Contributor III

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

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!

wardzynski
New Contributor III

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

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

Hi,

maybe also possible:

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

hope this helps

regards

Marco

Community Browser