Jump to content
We've recently updated our Privacy Statement, available here. ×

Count if or other functions to filter


Erwin Brinkhuis

Recommended Posts

Hello,

I have a table with a total of 100.000+ records with serveral columns. One of those columns consist of optional 10 different text data items.

In a cross-table I want to filter out 6 of those text data items from that column and take the total of that column against the total of records (percentage).

I tried with the formula:

count(if([Column1]="one of those 6 text data items",[Column1],null))

This works but it counts only one of those 6 text data items. I want 6 text data items in total. So with my logical sense.

count(if([Column1]="one of those 6 text data items","second of those 6 text data items","third of those text data items", "etc"[Column1],null))

But Spotfire returns only errors.

So my question: How can I make a total of those 6 text data items in one column in a cross-table and how can I take a percentage of that column against the total of 1000.000+ records

Hope someone can help

Link to comment
Share on other sites

To get the count :

sum(if ([Column1] = 'text1' or[Column1] = 'text2' or[Column1] = 'text3' or ....[Column1] = 'text6' ,1,0))

To calculate percentage:

sum(if ([Column1] = 'text1' or[Column1] = 'text2' or[Column1] = 'text3' or ....[Column1] = 'text6' ,1,0)) / count()

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...