# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## calculating number of minutes between incoming hour and '16:15'

Hello,

I am trying to calculate the number of minutes between an incoming timestamp e.g. '

 31-dec-14 08:05:00

and the fixed hour '16:15'

Defined some calculations in the load script:

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format

Testresult "interval static" returns what I expected (240 min), but when trying to apply the same logic to my real time in, i am unable to get the correct answer.

Any ideas on how to do this? Thanks for your help and time.

18 Replies
MVP

## Re: calculating number of minutes between incoming hour and '16:15'

May be this:

Interval((Time#('16:15', 'hh:mm') - Frac(TimeIn)), 'hh:mm:ss') as Interval

MVP

## Re: calculating number of minutes between incoming hour and '16:15'

if your field is like  31-dec-14 08:05:00

interval(maketime(16,15) - frac(date#('31-dec-14 08:05:00', 'DD-MMM-YY hh:mm:ss')))

interval(maketime(16,15) - frac(date#(field, 'DD-MMM-YY hh:mm:ss')))

MVP

## Re: calculating number of minutes between incoming hour and '16:15'

Hi,

Try like this

*,

(MakeTime(16,15) - Frac(Date#(TimeFieldName, 'DD-MMM-YY hh:mm:ss')))  * 24 * 60 AS DurationInMinutes

FROM DataSource;

Note: Replace TimeFieldName with actual field name.

Regards,

Jagan.

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes

However, the DurationInMinutes column does not show any values.

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes

However, the DurationInMinutes column does not show any values.

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your suggestion but the calculated field does not display any values... i modified the load script:

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

(MakeTime(16,15) - Frac(Date#( "Sessie - OK zaal in tijdstip" , 'DD-MM-YYYY hh:mm:ss')))  * 24 * 60 AS DurationInMinutes

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

Not sure you need the DATE#

The field seems to be recognised as date time already.

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your response. Blanks are gone but i get a negative and even the absolute value is larger then expected.

Not applicable

## Re: calculating number of minutes between incoming hour and '16:15'

does the FRAC function around maketime(16,15) help?