6 Replies Latest reply on Jan 8, 2013 6:37 AM by Brad Llewellyn

    Calculation ignoring a filter range

    Brad Llewellyn



      I have a worksheet of values and I would like to calculate an average and standard deviation of these (already aggregated) values.  However, I would also like to filter the top values by whether or not they are above 3 standard deviations from the mean.  The problem is, if I try to use Window_Avg and Window_StDev (with parameters set to be FIRST() and LAST()), it says the field is invalid.  This is probably due to the fact that you can't filter by a table calculation, because of a circularity issue that would leave you with no values displayed.  I would much rather use the built in AVG and STDEV functions.  However, you can't use these on aggregated values.  When I try to use TOTAL( SUM( ) ), I cannot find an appropriate by variable to cause it to return a single value equal to the sum of all rows in the table.  To make things worse, I do not want to return a sum of all rows in the table, I want to return a sum of all values in a set, and then display only a subset of that set.


      Here is a list of goals I am trying to accomplish.



      Display only [Elements] which have [Average Spend per Household] >

      Mean( [Average Spend per Household] ) + 3 * StDev( [Average Spend per Household] )



      (Accomplishing any of these goals may allow me to solve the rest of the issue on my own)


      1) Calculate a sum/average/standard deviation of all values displayed by a select group of filters, then filter on those values.

      (The first set of filters could be moved to the WHERE clause in the Custom SQL if necessary, meaning that we would only need to be able

      to filter on a sum/average/standard deviation of previously aggregated values.)


      2) Calculate a value on 1 sheet, then pull that value as a constant into another sheet.

        • 1. Re: Calculation ignoring a filter range
          Robin Kennedy



          I believe I have achieved your primary goal of being able to filter elements above 2 standard deviations (I assume you mean 2, not 3 as there are no elements 3SDs above the mean). I created a new calculated field which used your formula as you wrote above and it produces a True/False result which I put onto the filter shelf. (I also added a parameter so you can change the number of SDs)


          Unless I'm reading something wrong, your secondary goals should be easily accomplished by setting your filters on the first sheet to be at a 'Global' level. I.e. you set up a group of filters to show you the elements you want and then when you go to the next sheet, those same filters apply automatically and you can drill down to further details?


          Hope that helps,


          • 2. Re: Calculation ignoring a filter range
            Brad Llewellyn

            Thanks so much Robin.  I have one question, though.  I tried this approach earlier and found that the window calculation is affected by filters, i.e. filter out wednesdays and the window calculation doesn't consider wednesdays.  How did you know your window calculation would calculate before the filter on the set?

            • 3. Re: Calculation ignoring a filter range
              Robin Kennedy



              Might be misunderstanding you here, but window calculations always occur after the standard calculations and filters have applied... so if I take out Wednesdays, I get a different set of results -- i.e. the mean is different, as is the SD, and therefore the cutoff is recalculated and applied. I.e. you now see elements which are more than 2SDs above the mean for spends on all days except Wed


              Is this not the desired outcome?

              • 4. Re: Calculation ignoring a filter range
                Brad Llewellyn

                Hehe.  It seems you have misunderstood me.  For some reason, I cannot edit your "Set 1" to see exactly what you did.  I suspect that when you created the set, you told it to filter when the window calculation returned true.  Is this correct?

                • 5. Re: Calculation ignoring a filter range
                  Robin Kennedy

                  Nope.. Set 1 was already in there in the original twbx that you posted. I didn't do anything with that. The filter I added is a calculated field called More than Cutoff

                  • 6. Re: Calculation ignoring a filter range
                    Brad Llewellyn

                    So by putting the result of the window calculations in the filter tab, you forced the window calculation run before the filter?  If so, that is clever.  Thank you very much.