Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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
Hi Sunny.
Here a demo to better explain the situation.
thanks in advance
Eva
Marcus it works!But I didn't uderstand how it's possibile,... can u explain me why my previous solution didn't work?
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
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