11 Replies Latest reply: Nov 9, 2011 8:49 AM by Patrick Callocchia RSS

    Set Analysis with Aggr and formula

      I am starting with this Set Analysis using Aggr


      sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61"}>}active_pct),tenure)))


      This works fine. Now I am trying to replace active_pct with the formula


      avg(active_pct) - (2 * Stdev(active_pct)


      I have tried the followiong that do not work:


      sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61"}>} (avg(activ_pct) - (2*Stdeve(active_pct)))),tenure)))


      Sum({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61">}active_pct),tenure))) - (2 * Stdev({<tenure = {"<61"}>}(aggr(avg({<tenure = {"<61">}active_pct),tenure))) - This one is subtracting at too high of a level, I need the subtraction before the aggregation. 


      I have tried to create a variable for the formula so I can just plug the variabe into the set analysis, but I can't get the variable to work, the variable always totals instead of giving a value for each individual tenure value. 


      I tried to break this down to ensure the individual pieces work:


      Using avg(active_pct) - (2 * Stdev(active_pct) if I total by Sum of Rows I get the correct number if I select tenure value of 1 - 60


      Any help would be appreciated.