Count if or other functions to filter

[Erwin Brinkhuis]

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

[Shanid A K]

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()

[Erwin Brinkhuis]

Hi Shanid,

 

Thanks a lot with your feedback. This helps me a lot onto getting the correct figures and formula understanding. 

 

Kind regards,

 

Erwin