Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

You can succeed best and quickest by helping others to succeed. Join the conversation.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Interval function not working when formatting to h...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2016-09-15
07:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Interval function not working when formatting to hours

I have below expression that is assigned to a text object:

=Sum(

Aggr(

if([Order Type Operaciones] ='A' and not(IsNull([Equipment Operaciones])) and Len(Trim([Equipment Operaciones]))>0,

if(

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity]) > 1

,

(

Interval(MaxDateBS - Min({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Actual start (date)]), 'h')

) / Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity])

,

if (

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity]) = 0

,Interval(MaxDateBS - MinDateBS, 'h')

, if (

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>}[Operation/Activity]) = 1

,Interval(MaxDateBS - MinDateBS, 'h') / 2

,0

)

)

))

, [Equipment Operaciones]

)

)

/ Count({$<[Order Type Operaciones] = {'A'}, [Equipment Operaciones]={"=Len(Trim([Equipment Operaciones]))>0"}>} DISTINCT [Equipment Operaciones])

When using Interval function within this expression, from example:

Interval(MaxDateBS - MinDateBS, 'h')

Despite indicate the 'h' parameter, it is returning me the result in days.

However if the same expression (=Interval(MaxDateBS - MinDateBS, 'h')) is assigned to another text object, it works well.

So why?

Is there any other approach to get the difference between two dates in hours?

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LAST EDIT - FINAL SOLUTION - 2016.09.16

Note that MaxDateBS and MinDateBS are pure dates in the format 'DD/MM/YYYY'. I** suppose difference between MaxDateBS and MinDateBS are always in days, am I right? Could you confirm me this, please?**

Using Interval(Sum(Aggr(.......)), **'h'**) is not working, that is, not returning value in hours, so I have used Interval(Sum(Aggr(.......)),** 'd'**) * 24

=**Interval(**Sum(

Aggr(

if([Order Type Operaciones] ='A' and not(IsNull([Equipment Operaciones])) and Len(Trim([Equipment Operaciones]))>0,

if(

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity]) > 1

,

(

**MaxDateBS - Min({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Actual start (date)])**

) / Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity])

,

if (

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>} [Operation/Activity]) = 0

,**MaxDateBS - MinDateBS**

, if (

Count({$<[Order Type Operaciones] = {'A'},[Actual start (date)]={'>=$(=MinDateBS)<=$(=MaxDateBS)'}>}[Operation/Activity]) = 1

,(**MaxDateBS - MinDateBS**) / 2

,0

)

)

))

, [Equipment Operaciones]

)

)

/ Count({$<[Order Type Operaciones] = {'A'}, [Equipment Operaciones]={"=Len(Trim([Equipment Operaciones]))>0"}>} DISTINCT [Equipment Operaciones])**,'d') * 24**

1,287 Views

1 Solution

Accepted Solutions

2016-09-15
07:45 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

The Interval function only formats the value as hours. The actual numeric value will still be a fraction of a day. So if you want to calculate with a number hours then you need to multiply it by 24.

9 Replies

swuehl

MVP

2016-09-15
07:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try applying the interval() function around your complete aggregation:

=Interval(

Sum(Aggr( ....))

,'h')

998 Views

2016-09-15
07:45 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

The Interval function only formats the value as hours. The actual numeric value will still be a fraction of a day. So if you want to calculate with a number hours then you need to multiply it by 24.

Not applicable

2016-09-15
07:56 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

but why below expressions alone are working ok then?

for example:

= Interval(MaxDateBS - MinDateBS, 'h') when difference is one day, is returning 24 (hours)

and

= Interval(MaxDateBS - MinDateBS, 'd') when difference is one day, is returning 1 (day)

I do not understand at all.

but within the big expression it does not work, I need to do what you say:

Interval(MaxDateBS - MinDateBS, 'd') * 24

I do not understand what is the difference, why alone is working and within big expression not.

998 Views

2016-09-15
08:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It's because when you use it in a text box you see the formatted result. If you use it in another expression then the real numeric value is used, not the formatted value.

Not applicable

2016-09-15
09:01 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

very good explanation, so from my expression, Could I replace Interval(....) by simple difference between dates, that is, for example:

Interval(MaxDateBS - MinDateBS, 'd')

by:

MaxDateBS - MinDateBS

and the final result, as it is in days (I supponse MaxDateBS - MinDateBS is in days) multipying it by 24? Would it be correct?

Or another option, multiplying all the Interval results in days by 24¿?

998 Views

2016-09-15
01:48 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, that's correct. You could also use the interval function around the sum like swuehlposted above. That way the final value is still in fractions of a day, but formatted by the interval function to show as hours.

998 Views

Not applicable

2016-09-15
05:01 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Applying Interval around the sum like Stefan posted is not working in my case. Finally, I have multiplied each Interval(...,'d') within expression by 24. Doing this is working.

998 Views

swuehl

MVP

2016-09-15
05:15 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You would need to apply the formatting to the result of the numeric calculation, so in your case to the divison of the sum(Aggr()) and the Count() function.

Have you tried also this?

Interval() is a formatting function, as already mentioned by Gysbert. It only changes the text representation of a dual value, not the numeric value. Hence it doesn't make sense to use it in inner calculation steps. A following operation may remove the formatting again.

For example, you can format the number 43210 as Date

=Date(43210)

But if you want to get the following date,

=Date(43210)+1

return the number 43210 again.

Hence only use formatting functions like Date() or Interval() as most outer function / last step in your formula.

Having said this, you are of course free to transform your intervals from the internal numeric representation using fraction of days to hours (multiplying by 24).

It may just make it harder to apply QV date and time functions to these values without doing a division again.

998 Views

Not applicable

2016-09-16
03:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Ok, I have edit my post, see it below line "'----------------". Using this final solution I have posted works. I have removed inner Intervals.

Note that MaxDateBS and MinDateBS are pure dates in the format 'DD/MM/YYYY'. So I** suppose difference between MaxDateBS and MinDateBS are always in days, am I right? Could you confirm me this, please? **So the final result will be in hours**, so apply I apply interval(Sum(Aggr(....)),'d') * 24**

Using Interval(Sum(Aggr(.......)), **'h'**) is not working, that is, not returning value in hours, so I have used Interval(Sum(Aggr(.......)),** 'd'**) * 24

998 Views