Filtering data by group's average

[Rad]

Hi all,
I’m currently struggling with below QS filtering task. I have a set of data with processing minutes per item. Each item is assigned to a specific group, so i calculated the average minutes values per specific group:

avgOver(Minutes,[GroupId],PRE_AGG)

I want now to filter out all the items, which are above the groups average using controls preferably (outliers). I’m aware it can be done manually by setting manually a value for minutes but i’m thinking if it’s doable to filter out by some function which compares minutes per item and compares to the group the item is assigned too?

Was thinking of ifelse() function, but it works only when minutes are hard-coded:

ifelse(Minutes>10, ‘Outlier’, ‘Regular’)

When i’m trying with such a funtion, it’s added to QS but i cannot use it at filter/control level due to some technical error:

ifelse(Minutes > avgMinutesPerGroup, ‘Outlier’, ‘Regular’)

However, when using that calculated field as a regular column, it displays properly:

Is there better solution for this task which could make it work? Would appreciate your help on this.

[Max]

To get the average without hardcoding the minutes in you would just take out the partition you have.

avgOver(Minutes,[],PRE_AGG)

So it might look like this.

ifelse(avgOver(Minutes,[GroupId],PRE_AGG) > avgOver(Minutes,[],PRE_AGG), ‘Outlier’, ‘Regular’)

In regards to your filter can you just hardcode that you want ‘Outliers’?

[Rad]

thanks @Max custom filter works but is there any solution so i have dropdown list? By doing it like this user will have to input values manually under control:

Was thinking of parameters, could it solve it?

[Rad]

@Max besides that, is there any way to set a filter which shows values 2x,3x,4x above average etc.? I mean is there any way to create such a calculation and include this into dropdown?

[Max]

Yes parameters would solve it. You would need to just hardcode the parameters and then have that field equal to the parameter.

In regards to showing 2x,3x,4x I don’t think the actual values would work, but your could again hardcode the 2x,3x,4x values into your parameter list and have it be compared to those.

[Rad]

Hey @Max as for the parameters, here’s what i’m struggling with:

1.Filters with hardcoded parameters work fine, but cannot use them as controls (don’t have “pin to top” option)

2. Tried with custom filter list and can use it as control but no chance to have values as dropdown to select. I can have to separate controls but they will have to be used separately.

3. I was trying to link control to a calculated field but having same issue as for filter. Is it because calculation is too heavy for QS to proceed?

[Rad]

@Max please disregard, i’ve managed to find the suitable solution, thanks.