Interval function not working when formatting to hours

[#1]

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

[swuehl]

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

=Interval(

Sum(Aggr( ....))

,'h')

[Gysbert_Wassenaar · Accepted solution]

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.

[#4]

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.

[Gysbert_Wassenaar]

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.

[#6]

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

[Gysbert_Wassenaar]

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.

[#8]

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.

[swuehl]

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.

[#10]

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