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

    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


      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?