4 Replies Latest reply on Apr 13, 2018 6:17 AM by Branden Kornell

# Count number of conditions met and subtract from other variable

Hi I'm trying to count the number of conditions met (via series of IF conditions) and subtract that from another variable.

[Interval] -

SUM(

IF ([Received]<#12/11/2017# and [Actioned]>#07/11/2017#) THEN 1 ELSE 0 END)

IF ([Received]<#12/03/2018# and [Actioned]>#12/03/2018#) THEN 1 ELSE 0 END)

...

)

It's giving me a syntax error. Wondering if possible, how? Thanks.

• ###### 1. Re: Count number of conditions met and subtract from other variable

At a quick glance, you have an closing parenthesis with no opening one.

[Interval] -

SUM(

IF ([Received]<#12/11/2017# and [Actioned]>#07/11/2017#) THEN 1 ELSE 0 END)     <-- This parenthesis closes the SUM() on the line above.

IF ([Received]<#12/03/2018# and [Actioned]>#12/03/2018#) THEN 1 ELSE 0 END)

...

)

You would also need something like a + to connect your multiple IF statements.

• ###### 2. Re: Count number of conditions met and subtract from other variable

Yegor,

Add ELSEIF statements after your first IF statement and don't embed ELSE statements in each line. The edits I've made will return a 1 each time it meets the condition and then at the end, if it doesn't meet the condition, we return 0.

SUM(

IF ([Received]<#12/11/2017# and [Actioned]>#07/11/2017#) THEN 1

ELSEIF ([Received]<#12/03/2018# and [Actioned]>#12/03/2018#) THEN 1

ELSE 0

END

...

)

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Count number of conditions met and subtract from other variable

Hi Wesley (and Bradley), thanks but what I'm trying to do is get a sum of the number of conditions met not just which one:

So, if it meets the first or the second then -> [Interval] - 1, if it meets both conditions -> [Interval] - 2.

Would this be possible? Thanks.

• ###### 4. Re: Count number of conditions met and subtract from other variable

Taking a second look at it, your error (a common one) is that you have the first line of the expression ([Interval]) at the row-level, and the rest aggregated (SUM...). Tableau can't do both at the same time.

You should aggregate or disaggregate the whole thing. To aggregate everything, the SUM() would go on the outside. Try

SUM(

[Interval]

- IF ([Received]<#12/11/2017# and [Actioned]>#07/11/2017#) THEN 1 ELSE 0 END

- IF ([Received]<#12/03/2018# and [Actioned]>#12/03/2018#) THEN 1 ELSE 0 END

...

)