7 Replies Latest reply on Feb 8, 2019 11:50 AM by Michel Caissie

# different filters in separate colums

Hello,

I have a data set which has 3 columns. One of is district (values:1,2,3,4,5), the other column in called matched (values: matched, unmatched) and the last column is the percentage (values:0-100)

I need to create a table where the first column is the district number, the second number is the total people in the district (e.g., 1=30, 2=40, 3=55, 4=66), the third column is the total number of people who have been matched and the last column is the total number of people who have been matched and are above 70%.

The problem is once I apply the filter to get the third column, it wants to apply them to all other columns, that is it changes the values in the second column which was the total.

I have also tried to create a calculated field like the following :

if [matched] == 'matched' THEN COUNT([district])

END

and it gives me the following error:

cannot mix aggregate and non-aggregate comparisons or results in 'if' statement

Are there any other ways where I can apply these filters on each column individually?

• ###### 1. Re: different filters in separate colums

To correct the error you can do this:  if ATTR([matched]) == 'matched' THEN COUNT([district])

You could also do a calculation like this.  IIF( [matched] = 'matched',1,0)  The value of this will be the number of 'Matched' rows as well.

• ###### 2. Re: different filters in separate colums

Zahra,

Try putting your logic at row level  then aggregate the results

countd(if [matched] = 'matched' then [district]  end)

Michel

1 of 1 people found this helpful
• ###### 3. Re: different filters in separate colums

Hi

Not with filters -but you would try using sets - filters limit the data available to the worksheet - but sets create In and Out categories - its all there - it just has different classifications -

so if you can create sets on your different dimensions and then use the set interactions you might be able to do what you want -

Can't be more specific without seeing a twbx workbook

Jim

• ###### 4. Re: different filters in separate colums

Tim,

Though your first solution partially works,  it would not display  in the Total,   because for the Total  ATTR(matched)  would return undefined

and your second solution would not return the countd but the count

• ###### 5. Re: different filters in separate colums

In the first solution, you could switch out the ATTR with MIN or MAX

• ###### 6. Re: different filters in separate colums

Thank you it worked!

• ###### 7. Re: different filters in separate colums

Tim,

---In the first solution, you could switch out the ATTR with MIN or MAX

Well this is true only if you have the dimension  [matched] in the view.

But for the Total mark Tableau will execute the calculation on all rows  without taking into account the  granularity of the view.

So let say the  matched dimension have  3 values     aaa,  matched  and  zzz.

ATTR  would be undefined because there is not a unique value

MIN would return aaa    , so not equal  'matched'  and the  count would return 0

Same thing with MAX  which would return zzz

When possible we should always apply the logic at the row level and then aggregate.

This way, whether the dimension is in the view or not,  the Countd  will work