Multiselect conditional counting

[Emiel Parmentier]

Hey everyone

The spotfire formulas are still quite new for me and I don't think I can figure this one out on my own.

I've created a multiselect box that is linked to the unique entries of a certain column. I would like the user to be able to select any combination from the list and spotfire should then count the amount of samples that meet all criteria. So the criteria are as follows:

Each unique sample (specified in the first column) should be counted if all categories (2nd column) that the user has selected match a 'Yes' (boolean) value in the third column.

In my example file, when selecting Sample A & B there should only be one count. For example I've attached a spotfire file.

Hopefully someone can point me in the right direction.

[Khushboo Rabadia]

Your calculated column expression seems to be almost right. You just need to add one more condition about "YES" using "AND" operator in expression

Count(If("$map("${SelTarget}", ",")" ~= [Category] and [Y/N]="YES","ok",null))

[Emiel Parmentier]

Thanks for the suggestion Kushboo, whilde indeed another condition is needed, it still doesn't quite get me where I need to go. The problem is that I need to count within each unique sample (specified in the first column).

After lots of tinkering I actually managed to solve it. I'll post my solution for future reference.

First I calculated a column in which the amount of correct criteria are counted OVER the sample ID. (Over being a crucial part for me in this step.)

(Count(If("$map("${SelTarget}", ",")" ~= [Category] and [Y/N]="YES","ok",null)) OVER([sampleID]))In the second column I calculated the amount of Unique samples that match the count of the multiselect.

Count(DISTINCT If([CountCriteria]=Integer($map("if(Len('${SelTarget}') >0, 1, 0)", "+")),[sampleID],null))This last column finally gives me the value I want.

It's pretty convoluted and I'm sure there must be a simpler way. So if anyone has any suggestions for a more elegant solution, I'd always like to hear it.But I'm happy for now.