Dynamic Sets (Top N and Grouping Others)

[Vincent Thuilot]

Hello,

I would like to request enhanced support for dynamic sets in Spotfire.
Specifically, I am looking for improved functionality to dynamically display the details of the top N items in a bar chart and group the remaining items as "Others" based on a specified criteria.

Currently, the existing features in Spotfire provide some options for achieving this, but I believe that additional capabilities or enhancements in this area could greatly improve the user experience and analytical flexibility.
My current workaround is like this: create a ranking column with the initial criteria that i need, for instance: DenseRank(Sum([Sales]) OVER ([Product)]),"desc",[Country]) and then use another calculated column for attributing Other like this: "case   WHEN [Ranking]<5 then [Product] ELSE "Other" END".

It does the job but I'm wondering if something more dynamic (ie, working when using filters for instance) exist or could be set in place.
As you can imagine, it can be quite cumbersome when dealing with large datasets or with multiple sets of column necessitating the "Other" grouping in various visualizations.

Happy to hear your insights/ideas on this.

Just in case, i have submitted it as an idea here: https://ideas.spotfire.com/ideas/TS-I-9666

[Gaia Paolini]

Thanks, but I don't understand the connection between your explanations and the screen shots. 

[Gaia Paolini]

would this Mod help?

 

[Vincent Thuilot]

Hi Gaia

In the screenshots (i forgot to annotate them, sorry!), what you can see is the current result when nothing is done: we have lots of colors and can't regroup the small entities as Other.

I'm attaching what we need to achieve, where we could only see the top N (here top 4) and the rest is seen as Other (grey).
As you can see, the charts are more digestible compared to what i shared earlier.

To perform this is went through the steps i detailed above but it's not really dynamic (sensitive to filtering) and it needs 2 calculated columns.
Suppose i need to break-down the Top N and Others in X other categories, i would need to create 2*X columns and X charts. Quite cumbersome I think.

 

Tableau is achieving this through what they refer as dynamic sets (https://help.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm).
 

Hope it helps to better understand.
Please let me know if anything is still unclear.

Vincent

[Gaia Paolini]

There is a built in way to regroup. If you have a bar chart with the original categories, you can mark the ones you want to add to the Other group, right click and select "Group from Marked Categories". I am not sure how it would work in your case, could you try? Behind the scenes it creates a (grouped) column.

Edited July 10 by Gaia Paolini

[Vincent Thuilot]

HI Gaia,

Indeed, but that would be quite manual work and not super user friendly, for instance how to select the top ten manually from a bar chart like this?

Not easy, right? Even when sorted out, it's difficult. Maybe there are no workarounds today.

Some of our services are particularly asking for this kind of stuff and I also face these situations at time when having a dynamic behavior to create sets would enhance the interactivity.

[Gaia Paolini]

try with a bar chart that has the x axis set to the column you want to group by. Then order by values.
If it does not work, can you post a sample dxp?

[Gaia Paolini]

examples in screenshots: Movies dataset, grouping the Director column. Then using it to colour the Rating.
(sorry the after is pasted before the before.. seems alphabetical order of screenshots)

Edited July 10 by Gaia Paolini

[Vincent Thuilot]

Thank you Gaia,
you got the point, however in your example it's still a very manual process.

How to make this more dynamic:
- if a filter is applied
- if in your example we don't look at row count but other metrics like sales, profit, etc...and get a visual dynamically updated.

Does this make sense to you?

Best 
Vincent

 

 

 

 

[Gaia Paolini]

understood. It does not look that non-manual on Tableau though.

[Gaia Paolini]

I can try to semi-automate it with an Iron Python script. In this example I am selecting the top 5 values from the "Director" column in the "Movies" table, based on frequency.
The input parameters are currently hard coded but can be made dynamic of course. The script creates a new column called 'Director_GROUPED' (if it already exists, he removes then recreates it).

Notes:

- you need to run the script every time you change the filters
- it ignores values of Director with the same frequency in the selection of the top n (can be fixed)

- it does not re-group empty values (I think this is the correct behaviour, so we keep them separate)
- it does react to filters, but then changes all the values of the column, not just the filtered ones. (I don't know how this could be changed).

Attaching an example Spotfire dxp (version 14.0)
This is the script: let me know if it goes some way towards your requirements. 

from Spotfire.Dxp.Application.Filters import * 
from Spotfire.Dxp.Data import *
from itertools import groupby
from collections import Counter

# Input parameters
target_name = 'OTHER'
table = Document.Data.Tables['Movies']
my_column = 'Director'
new_column = my_column+'_GROUPED'
top_n = 5

# Get a specific filtering or the active one
# filtering_scheme_type possible values: ['specific','active']
filtering_scheme_type = 'active'
if filtering_scheme_type=='specific':
	filtering_scheme = Document.FilteringSchemes[Document.Data.Filterings["Filtering scheme"]]
else:
	filtering_scheme = Document.FilteringSchemes[Document.ActiveFilteringSelectionReference]
filter_collection = filtering_scheme[table]
filtered_rows = filter_collection.FilteredRows
row_selection = RowSelection(filtered_rows)

 
# Iterate over the filtered rows and collect column values
cursor = DataValueCursor.CreateFormatted(table.Columns[my_column])
values = []
for row in table.GetRows(filtered_rows,cursor):   
	value = cursor.CurrentValue
	values.append(value)

# Straight count
#counter_dict = {x:values.count(x) for x in values}
#counter_dict = {value: len(list(freq)) for value, freq in groupby(sorted(values))}
counter_dict=Counter(values)
selected_keys = dict(counter_dict.most_common(top_n))

# Generate CASE expression
cases = []
for key in selected_keys.keys():
	case = " WHEN ["+my_column+"] = '"+key+"' THEN ["+my_column+"] "
	cases.append(case)
case_expression = "CASE "+" ".join(cases)+ "ELSE '"+target_name+"' END"

# Add or update calculated column
cols = table.Columns
try:
	cols.AddCalculatedColumn(new_column,case_expression)
except:
	cols.Remove(new_column)
	cols.AddCalculatedColumn(new_column,case_expression)

 

example_set_14.0.dxp

Edited July 11 by Gaia Paolini

[Vincent Thuilot]

Hello Gaia,
thanks a lot for sharing, this is absolutely great work! You totally understood the logic of it and was able to make it real.

The main difference with Tableau is that in Tableau this is managed through an out-of-the-box menu that makes it quite straightforward and flexible.
Interestingly, each created set there is automatically available in the data pane and visible as a "set" so one can see it was created on top of the existing dataset.
In addition to this, one can also combine two sets when meaningful, this can be quite powerful.

Spotfire would benefit so much from having something similar to perform analyses, where sets could be created in a very efficient way without having to do much programming.
Please don't get me wrong, the solution you provide is a already good workaround. I just wanted to share that there is a great opportunity to enhance analytical capabilities here.

Thanks again for the awesome support!

Vincent

 

[djones]

Just want to add that this was a great conversation and I too am looking for more seamless out of the box solutions to mimic Tableau's sets/groupings in Spotfire. Especially creating a group based on multiple columns/criteria.