How to count values across multiple columns in Spotfire similar to CountIf function

How to count values across multiple columns in Spotfire similar to CountIf function

Suppose you have four columns of Year (Year1, Year2, Year3, Year4) and you want to count the occurrence of Year1 column's value that is present in all four columns. Take the example data: RegionYear1Year2Year3Year4MW2013201420152016NE2013201420152016SE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2016201720182019WE2014201520162017WE2014201520162017WE2014201520162017WE2014201520162017WE2014201520162017WE2014201520162017WE2014201520162017WE2013201420152016WE2013201420152016NE2013201420152016SE2013201420152016NE2013201420152016SE2013201420152016NE2013201420152016SE2013201420152016NE2013201420152016SE2016201720182019NE2016201720182019SE2016201720182019 In the case of 2016 from Year1 column, there are 14 occurrence in Year1, 7 occurrence in Year3 and 11 occurrence in Year4 column so the desired total would be 14+7+11 which equals 32. In the same way, you want to count the occurrence of every distinct value from Year1 out of all four columns. To achieve this you can use either of the two approaches:

Option 1) Use an unpivot transformation and transform the 4 year columns to one column:

Select Insert > Transformations...
   Added transformations
        Transformation name: Unpivot
        Columns to pass through: Region
        Columns to transform:
            Year1
            Year2
            Year3
            Year4
        Category column name: Year
        Category column data type: String
        Value column name: Value
        Value column data type: Integer
        Empty values included: No

Then then calculate the value for each year value using an OVER function in an expression like:

Count() over ([Value])

Option 2) Use a regular expression function like RXReplace() in a calculated column:

Len(RXReplace(RXReplace(Concatenate([Year1]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year2]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year3]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year4]),String([Year1]),"$","g"),"[^$]","","g"))

Explanation: First concatenate the values in each year column, then search for the Year1 value in that concatenated set. If the Year1 value exists then replace it with a $ character (or other unique single character not present in the values) for all occurrences. Then perform the replace operation again where everything except $ is replaced with the empty set "". At this point the string is only left with $ characters, one for each occurrence, which can be easily counted. Use the Len() function to sum it up across each column to get total year value occurrence.