Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andyscslots
New Contributor III

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

Thanks in advance

1 Solution

Accepted Solutions
andyscslots
New Contributor III

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

I answered my own question as follows

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

6 Replies

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

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

dwforest
Valued Contributor

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

untested but:

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

andyscslots
New Contributor III

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

I answered my own question as follows

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

Highlighted
andyscslots
New Contributor III

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

Hi David,

why the zero at the end?

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

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

dwforest
Valued Contributor

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

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

Community Browser