Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jorge3fernandes
New Contributor

Excluding Outliers from Avg Calculations at the dimension level

All,

I need to plot monthly average trend. Each month behaves differently hence the need to exclude outliers at the month level. This calculation needs to be dynamic if I need to look at the trend by a certain category (the outliers need to be recalculated at the category level by each month)

I have attached a sample QVF.

7 Replies
MVP
MVP

Re: Excluding Outliers from Avg Calculations at the dimension level

Is the outliers in one direction only or do we have outliers in both directions?

jorge3fernandes
New Contributor

Re: Excluding Outliers from Avg Calculations at the dimension level

‌yes. I just want to exclude the upper outliers.

MVP
MVP

Re: Excluding Outliers from Avg Calculations at the dimension level

May be this

Avg(Aggr(If(Values <= Fractile(TOTAL <Cat, Dates> Values , 0.75) +  1.5 *(Fractile(TOTAL <Cat, Dates> Values , 0.75) - Fractile(TOTAL <Cat, Dates> Values , 0.25)), Values), Cat, Values, Dates))

jorge3fernandes
New Contributor

Re: Excluding Outliers from Avg Calculations at the dimension level

When I tested your solution, the average is higher than the normal average calculation. That shouldn't be the case if we're excluding the upper outliers, Otherwise it works, just not with the numbers I expect

MVP
MVP

Re: Excluding Outliers from Avg Calculations at the dimension level

I saw similar problem, but I am not sure what might be issue... Would you be able to share the raw data behind this app in an Excel file?

jorge3fernandes
New Contributor

Re: Excluding Outliers from Avg Calculations at the dimension level

Please find the raw data attached! I also noticed you're using Total in your outlier calculation and that will have a uniform outlier for all the dimension values. I want each dimension to have its own outlier

MVP
MVP

Re: Excluding Outliers from Avg Calculations at the dimension level

I am heading out for the day today, but will try to take a look today in the evening or tomorrow morning.

Best,

Sunny