topic Re: Unexpected result with Avg() in QlikView

Unexpected result with Avg()

hansdevr — Mon, 21 Nov 2016 08:04:36 GMT

Hi all,

I have the following bit of code in my load script:

If(IsNull([Datum sluiting doss.])<>-1, // Datum sluiting gevuld? Dan kan doorlooptijd worden gemeten.
floor([Datum sluiting doss.]) - floor([Datum melding]),
'') as [Doorlooptijd doss.],

Which checks for a dossier closing date to be filled. If so, it calculates the handling time of the dossier in the field [Doorlooptijd doss.]

In the designer I use this expression to calculate average handling time:

avg(
{$
<[Status doss.] = {'Gesloten'}>
}
[Doorlooptijd doss.])

As a result (on three closed dossiers) I get 0.3, while I expected 2.0

Note that [Status doss] = 'Gesloten' is the indicator of [Datum sluiting doss.] to be filled...

Any ideas?

Re: Unexpected result with Avg()

stabben23 — Mon, 21 Nov 2016 08:16:56 GMT

Hi Hans,

I'll think you will need to use aggr here, try this:

avg(aggr(avg( {$ <[Status doss.] = {'Gesloten'}> } [Doorlooptijd doss.]),[Datum registr.))

Maybe some more Dimension in Your aggr.

Re: Unexpected result with Avg()

jonathandienst — Mon, 21 Nov 2016 08:18:01 GMT

It looks like the records where [Datum sluiting doss.] is null are being included in the average. You don't say how the [Status doss.] field is defined, so its hard to say for sure.

You could try changing the expression for [Doorlooptijd doss.] to produce a null when [Datum sluiting doss.] is null, rather than producing an empty string.

Re: Unexpected result with Avg()

hansdevr — Mon, 21 Nov 2016 08:53:08 GMT

Hi, I tried your solution; it changed the outcome from 0.3 to 0.5, so still no luck...

avg(aggr(avg( {$ <[Status doss.] = {'Gesloten'}> } [Doorlooptijd doss.]),[Datum registr.]))

Re: Unexpected result with Avg()

hansdevr — Mon, 21 Nov 2016 08:58:28 GMT

Hi Jonathan,

[Status doss.] contains the status of the dossier, and can be 'Gesloten' (Closed), 'Lopend' (Open) and a few more.

[Datum sluiting doss.] is the date on which the dossier was closed. [Datum melding] = report date.

I tried to change the outcome of the doorlooptijd expression to null(), but to no avail...

Re: Unexpected result with Avg()

hansdevr — Mon, 21 Nov 2016 09:15:13 GMT

I have found the answer. For some stupid reason, zero values were suppressed in my straight table...