
1. Re: Calculation ignoring a filter range
Robin Kennedy Jan 8, 2013 3:14 AM (in response to Brad Llewellyn)Brad,
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,
Robin.

Book3.twbx.zip 22.4 MB


2. Re: Calculation ignoring a filter range
Brad Llewellyn Jan 8, 2013 6:07 AM (in response to Robin Kennedy)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 Jan 8, 2013 6:16 AM (in response to Brad Llewellyn)Brad,
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 Jan 8, 2013 6:20 AM (in response to Robin Kennedy)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 Jan 8, 2013 6:23 AM (in response to Brad Llewellyn)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 Jan 8, 2013 6:37 AM (in response to Robin Kennedy)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.