6 Replies Latest reply: Mar 3, 2015 4:45 PM by Michael Gardner

# Set analysis filter on function values within a range

Hello, I am trying to create a bar chart where the bars are the sum of the sales over a subset of entries selected based on the average margin.  For example, the data points used are like:

customermargin (gross)amount
a1020
a222
b-751
c515
c3737
c1428
d612
d-1019

I want to find the amount summed over the customers with the following attributes:

1. negative average margin percentage ie. customer b has an average -7/51
2. average margin perc. between 0% and 20%
3. average margin perc. between 20% and 50%
4. average margin perc. >50%

I have 1. and 4. ie.:

sum({<customer={"=avg(margin/amount)<=0"}>} amount)

but all attempts to create a range have failed ie.:

sum({1<customer={"=avg(margin/amount)>=.2"},customer={"=avg(margin/amount)<=.50"}>} amount)

sum({1<customer={"=avg(margin/amount)>=.2"} AND customer={"=avg(margin/amount)<=.50"}>} amount)

sum({1<customer={"avg(margin/amount)>=.2, avg(margin/amount)<=.50"}>} amount)

and many more but I've lost track and am obviously missing one particular version...

Bonus: how do I create a pie chart with this info without coding the margin range into the load statement?

• ###### Re: Set analysis filter on function values within a range

maybe this

sum({\$<customer={"=avg(margin/amount)>=.0"}*{"=avg(margin/amount)<=.2"}>} amount)

EDIT: PFA

• ###### Re: Set analysis filter on function values within a range

Thanks Massimo! This 'seems' to work-at least my numbers add up now...I used Year (I have it in my data set) as the dimension and then used your expressions.  One question so I can be comfortable with what this is doing:

What does the '*' between sets mean?

• ###### Re: Set analysis filter on function values within a range

* returns the set of records that only belong to the overlapping data from the first and second sets (similar to an INTERSECT in SQL)

• ###### Re: Set analysis filter on function values within a range

see attached

Regards

• ###### Re: Set analysis filter on function values within a range

Hello, thanks-this is nice and very close but I need to find customers with average margins in the given buckets as opposed to counting distinct instances.  I am trying to modify with the aggr function-I will post above...

• ###### Re: Set analysis filter on function values within a range

I have now also tried aggregated expressions:

sum(aggr(if(avg(margin/amount)>.2 and avg(margin/amount)<=.45,customer), amount))

which produces wildly large sums.  I just don't know how to approach this-calculated dimension, expression, aggregation...can't seem to get anything to return appropriate amounts.