Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
wardzynski
Creator
Creator

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
sunny_talwar

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

View solution in original post

20 Replies
sunny_talwar

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
Creator
Creator
Author

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
sunny_talwar

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

sunny_talwar

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

wardzynski
Creator
Creator
Author

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
Creator
Creator
Author

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

wardzynski
Creator
Creator
Author

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
Creator
Creator
Author

Sunny,

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

MarcoWedel

Hi,

maybe also possible:

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

hope this helps

regards

Marco