Idea: Can Subset( ) work on filtered data?

walsh · July 18, 2011 5:01PM

I'm monitoring project status. Each project comprises a number of activities that are assigned a RAG (Red/Amber/Green) status. If any of the project's constituent activities is marked as Red, then the project status is set to Red. If any activity is Amber (and none is Red), the Project status is Amber. I assign numeric values to each Activity's RAG status and select the maximum value (SUBSET_MAX()) for each project to set the colour of its Project (RAG) Status.

In the attached snippet, I have one project and three activities. The activities are, respectively, Red, Amber and Green. When I filter out the completed activity (Red), I was hoping to see the Project status change, as now there's only a Green and an Amber in play. That doesn't happen - the overall project status stays Red, even when I show only the open activity, which is Green.

Is there a way to make SUBSET() functions recalculate on just filtered data? Or am I going the wrong way about this?

tjbate · July 20, 2011 2:43PM

Kevin - Currently, functions/formulae evaluate across the entire ALL data set, not any brushed, filtered IN or otherwise interactively-defined subsets. Subset functions allow you to pre-define multi-field conditional subsets across which to apply a given formula, but they do not currently further react to ad-hoc filtering or brushing the way a Named Query can when intersected with filtering.

Presently, the only way to get more user interaction with Subset function results is to use Variables as values within the Subset formulae. This permits the end user to dynamically select one or more conditional value thresholds to define the subset (using only the pre-defined fields) across which the formula should be evaluated.

Have a look at the Add Variable options to see if there is a way to get close enough to what you want this way.

steve · August 8, 2012 12:40PM

I've reclassified this as an Idea, as the 'master idea' for the ability to have formulas recalculate for filtered data.

There is currently a workaround to achieve this using aggregation; see:
http://forums.visokio.com/discussion/625/formula-field-in-table-view/p1
http://forums.visokio.com/discussion/292/dynamic-subtotals/p1
http://forums.visokio.com/discussion/1618/difference-bars-bar-comparisons/p1

davedunckley · November 2, 2012 7:00AM

This would be really really helpful! We are coming up agenst this all the time now, and its creating massive hurdels for us.

Nick · September 29, 2014 3:53AM

Agreed. This is causing major problems for us too. A fix would be seriously useful.

Cheers, Nick.

Welcome!

Categories

Ideas Parade

Tagged