Unexpected result with Avg()

[hansdevr]

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?

[stabben23]

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.

[jonathandienst]

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.

[hansdevr]

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.]))

[hansdevr]

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...

[hansdevr]

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