Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## sum of total sales based on values below an average % range

Hi All.

I need a KPI to sum the total sales below 25% of the average sales. The average sales % calculates as follows

avg(sales)/avg(total sales).

for example if my sales average is 100 I need to sum up all the vales below 25

I'm sure it's a aggr function but I can't get it to work

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I answered my own question as follows

sum(aggr(if (Avg(Sales)/Avg(TOTAL Sales) <0.25,Sales ),Sales))

6 Replies
MVP

Would you be able to show what you have by sharing a sample or by showing some images?

Specialist II

untested but:

sum(if((avg(sales)/avg(ALL Sales)) <0.25, sales, 0))

Anonymous
Not applicable
Author

I answered my own question as follows

sum(aggr(if (Avg(Sales)/Avg(TOTAL Sales) <0.25,Sales ),Sales))

Anonymous
Not applicable
Author

Hi David,

why the zero at the end?

MVP

Aggregating this by sales? Not a great idea... but if it works for you... then feel free to use it

Specialist II

Just the false part of the If, not really necessary if Summing I suppose.