Problem with interval and aggr function

[19iv1987]

Hi, I have a problem using interval and aggr function together.

I calculated the "handling time" in this table

using interval function :

INTERVAL(only({..setanalysis..}DATE)

-only({.setanalysis.>}DATE),'mm')

and it works.

Now I want to represent the AVG Handling time (186 in the previous table) in a text box.

I used this formula:

avg( aggr(INTERVAL(only({..setanalysis..>}DATE)

-only({..setanalysis..} DATE),'mm'),CAMPAIGN_MEMBER_ID)) but I obtain a result completely different from 186.. like 0,125.. Anyone could explain me how can I obtain the right result and what am i missing??

thanks in advance

Eva

[sunny_talwar]

Would you be able to share a sample where you are trying this and also share the number you are expecting to see based on the sample you provide?

[marcus_sommer · Accepted solution]

I think you need to set the format-function on the outside like:

INTERVAL(avg( aggr(only({..setanalysis..>}DATE)

-only({..setanalysis..

} DATE),CAMPAIGN_MEMBER_ID)),'mm')

- Marcus

[19iv1987]

Hi Sunny.

Here a demo to better explain the situation.

thanks in advance

Eva

[19iv1987]

Marcus it works!But I didn't uderstand how it's possibile,... can u explain me why my previous solution didn't work?

[marcus_sommer]

Each time is only a part from a day so that for example 1 hour = 1/24 = 0,0416666666666667 which is the real value and something like 01:00:00 is only a string-representation respectively a formatting. Further each calculation with a formatted date/time returned only a pure numeric representation - this meant the formatting must be on the outside of the value/calculation if you want to show it with any date/time-formatting.

- Marcus

[swuehl]

Another option - if you want to use the minutes in further calculation as number - would be to transform the intervals represented as fraction of days into intervals represented as minutes:

=avg(aggr(

(only({<RANK={2}>}DATE)-only({<RANK={1}>}DATE))*24*60

,Members))

edit:

Or just applied to the average value

=avg(aggr(

(only({<RANK={2}>}DATE)-only({<RANK={1}>}DATE))

,Members))  *24*60 // transform fraction of days into minutes by multiplying with number of minutes per day