Qlik Community

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. Capture.PNG

18 Replies

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
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
MVP

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

Hi,

Try like this

LOAD

*,

(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'

Thanks for your suggestion, have modified load script as:

   

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

Capture.PNG

Not applicable

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

Thanks for your suggestion, have modified load script as:

   

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

Capture.PNG

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

Capture.PNG

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. Capture.PNG

Not applicable

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

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

Community Browser