CODEWITHSUNDEEP
calculated-columnsspotfire
NightLearner
NightLearner

Reputation: 305

Number of Successive Cells of a Same Value in Spotfire

I am wondering if it is possible to created a calculated column that will count how many cells of the same value are successive (i.e., next to each other) so that I can divide it by the total number of cells in that column. (I'm only looking at 1's and 0's in the column. Any help or advice would be greatly appreciated as I've been scratching my head over this for a while,

enter image description here

The percentages at the bottom of the columns is simply = (the number of 1's touching another 1) / (total # of rows) --> my end goal

Upvotes: 0

Views: 605

Answers (1)

S3S
S3S

Reputation: 25112

Ok this has to be done in a few steps.

  • Insert a calculated column RowId() and name it rid
  • Insert a calculated column with this expression, and name it [TouchingA]:

If(([Location A]=1) and (([Location A]=First([Location A]) over (Next([rid]))) or ([Location A]=First([Location A]) over (Previous([rid])))),1,0)

  • Insert a calculated column with this expression, and name it [TouchingB]:

If(([Site B]=1) and (([Site B]=First([Site B]) over (Next([rid]))) or ([Site B]=First([Site B]) over (Previous([rid])))),1,0)

At this point, your data will look like this:

SpotfireTouching

Next, insert a CROSS TABLE and set the Horizontal Axis = (Column Names), the Vertical Axis = (None), and the Cell Values = Sum([TouchingA]) / Max([rid]) as [A Percent], Sum([TouchingB]) / Max([rid]) as [B Percent]

This will show you the values you want, like below. Of course you can sett his in a calculated value in a text area or anywhere else you want with the proper IF or CASE statement. Lastly, you can change the formatting to make the value a % versus decimal.

enter image description here

Upvotes: 1

Related Questions