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.