topic Re: Interval function not working when formatting to hours in QlikView

Interval function not working when formatting to hours

Thu, 15 Sep 2016 11:22:23 GMT

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

)

))

, [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

)

))

, [Equipment Operaciones]

)

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

Re: Interval function not working when formatting to hours

swuehl — Thu, 15 Sep 2016 11:41:57 GMT

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

=Interval(

Sum(Aggr( ....))

,'h')

Re: Interval function not working when formatting to hours

Gysbert_Wassenaar — Thu, 15 Sep 2016 11:45:06 GMT

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.

Re: Interval function not working when formatting to hours

Thu, 15 Sep 2016 11:56:33 GMT

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.

Re: Interval function not working when formatting to hours

Gysbert_Wassenaar — Thu, 15 Sep 2016 12:15:10 GMT

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.

Re: Interval function not working when formatting to hours

Thu, 15 Sep 2016 13:01:35 GMT

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

Re: Interval function not working when formatting to hours

Gysbert_Wassenaar — Thu, 15 Sep 2016 17:48:00 GMT

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

Re: Interval function not working when formatting to hours

Thu, 15 Sep 2016 21:01:17 GMT

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.

Re: Interval function not working when formatting to hours

swuehl — Thu, 15 Sep 2016 21:15:23 GMT

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.

Re: Interval function not working when formatting to hours

Fri, 16 Sep 2016 07:53:42 GMT

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