workaround for nested aggregation

Anonymous — Mon, 28 May 2018 14:24:50 GMT

Hi everyone,

My case:

i'm calculation order duration based on various fields and am storing them in various other fields, more or less like this:

If(([Status]= '512'), (Today()-[OrdStartDate]))as OrdDurationActive,

If(([Status]= '1024'), ([OrdRS1024Date]-[OrdStartDate]))as OrdDurationWon,

I think I need to use this method because the duration is based on various fields. In practice, this leads to 4 nulls and one filled value, because based on the status, only one of 5 fields is filled. It isn't possible for more than one field to have a value.

This results in 5 different fields for different states. Now I want to create one average based on these various fields.

So in this case:

ID | Status | Active Duration | Won Duration

Order 1 | Won | - | 200 |

Order 2 | Active | 123 | - |

The average of these two should be (200+123)/2

How should I achieve this? I've played around with Sum(Avg(..)+Avg(..)), et cetera, but didn't get the right results yet...

Re: workaround for nested aggregation

Gysbert_Wassenaar — Mon, 28 May 2018 16:16:56 GMT

Perhaps like this: Avg(RangeSum([Active Duration],[Won Duration]))

topic workaround for nested aggregation in QlikView

workaround for nested aggregation

Re: workaround for nested aggregation