Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

workaround for nested aggregation

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

1 Reply
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand