Calculated Column That Returns Column Names of all Columns where Value Greater than X

[Nikita Goldovsky]

Total Time

Stage1

Stage2

Stage3

 

Stage4

 

CalcColumn

 

 

17

2

6

8

1

Stage2,Stage3

 

 

19

8

4

7

0

Stage1,Stage3

 

 

 

 

My data looks like the table above. I want to create a calculated column that will look through the "Stage" columns and return a list ofall column names that exceed a value of 5.

Can anyone suggest the best way to do thisin spotfire

[Erik Dahlman]

Given the data above this expression works:

Left($map("If($esc($csearch([Data Table], "Stage*"))>5, '$csearch([Data Table], "Stage*"),', '')", "&"),-1)

CSearch finds all columnnames in Data Table that begins with Stage. The if statement outputs the columname from the identical second csearch and a comma if the escaped column is > 5 and empty string if it is not. Map combines these strings with the concatenate operator & and left finally removes the dangling last comma.

If you have other columns named stage or they're nonnumeric you'll have to modify the search expression somewhat. Also note the hardcoded datatable name [Data Table].

[Nikita Goldovsky]

Thank you for that voodoo magic.