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
      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?