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:
| customer | margin (gross) | amount |
|---|
| a | 10 | 20 |
| a | 2 | 22 |
| b | -7 | 51 |
| c | 5 | 15 |
| c | 37 | 37 |
| c | 14 | 28 |
| d | 6 | 12 |
| d | -10 | 19 |
I want to find the amount summed over the customers with the following attributes:
- negative average margin percentage ie. customer b has an average -7/51
- average margin perc. between 0% and 20%
- average margin perc. between 20% and 50%
- 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?
