I have a table that has 11 columns that have a yes if the well is in that fund. One well can be in multiple funds. Is there a way to make a pie chart based on fund from a count of the yeses. See examples from excel below.

[Katie Sikorski]

Does anyone have any suggestion on how to do this in a calulcated column of and if statement. Thanks

[Gaia Paolini]

There is a way of forming an expression in Spotfire that allows you to select columns based on e.g. their names, then apply a function to them. In your example, you could try it either as a calculated column and then use it to size the pie chart, or directly on the pie chart.

The first bit searches for columns that do not contain DI_API in the column name (so you don't have to list all 11 columns):

[$csearch([data],"not DI_API")]

This would give you a list of columns, to which you apply a function that counts 1 whenever the column does contain something and then sums the elements of the list (as a boolean value turned into an integer gives 0 or 1):

$map("Integer((...) is not null)","+")

Putting it all together:

$map("Integer(([$csearch([data],"not DI_API")]) is not null)","+")

You can use it to calculate a column, say [Total], then use it to size the pie chart, OR use this expression directly in the pie chart. The pie chart would be coloured by the first column (DI_API14).

You will need to put Avg() around it in the size as it expects an aggregation.

If you want to select different columns you will change the string "not DI_API" to something else (you can use and and or search).

[Katie Sikorski]

Thanks I have been trying several options and I think I got it to work thanks so much for your help.