topic Re: Problem with interval and aggr function in QlikView

Problem with interval and aggr function

19iv1987 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem with interval and aggr function

sunny_talwar — Wed, 01 Feb 2017 12:37:02 GMT

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?

Re: Problem with interval and aggr function

marcus_sommer — Wed, 01 Feb 2017 12:53:17 GMT

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

Re: Problem with interval and aggr function

19iv1987 — Wed, 01 Feb 2017 13:33:11 GMT

Hi Sunny.

Here a demo to better explain the situation.

thanks in advance

Eva

Re: Problem with interval and aggr function

19iv1987 — Wed, 01 Feb 2017 13:37:21 GMT

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

Re: Problem with interval and aggr function

marcus_sommer — Wed, 01 Feb 2017 13:47:29 GMT

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

Re: Problem with interval and aggr function

swuehl — Wed, 01 Feb 2017 14:17:16 GMT

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