1 Reply Latest reply on Dec 28, 2016 9:52 AM by Adam.Cogswell

# Calculating IF and AND Statements with survey Data

Dear Tableau super users,

I am stuck with this survey data, which actually is a company scorecard to meassure different certification levels for a company program.
The countries are answering the questions (which are milestones they need to do in order to move up the certification level) via survey (Confirmit) and depending on the answers they give 1 or 0 (Y/N) they reach a specific level of certification.
I have two files the raw data and the helper file.
I need to build a condition that goes like this:

if answer to Q1 1 and Q1 2 and Q1 3 and Q1 4 all equal Y then certification Silver
if answer to Q1 1 and Q1 2 and Q1 3 and Q1 4 and Q2 1 and Q2 2 and Q2 3 all equal Y then certification Gold

if answer to Q1 1 and Q1 2 and Q1 3 and Q1 4 and Q2 1 and Q2 2 and Q2 3 and Q3 1 and Q3 2 and Q3 3 all equal Y then Certificaltion Platinum
and so on..there are over 100 questions actually

The thing is that if I separate the condition like this:
if answer to Q1 1 equal Y then certification Silver
it works, but when adding all conditions together with "AND" it is too restrictive and the calculated field is not working, get no return.
If I add them together with "OR", the rule works only partially, and of course it is not correct.

Do you have a suggestion about how to work with theses conditions?
I have tried to give the answers a number value, but that doesn’t work either..

I am attaching the twbx file and the explanation in excel about how it should work.

I can need all your help, thanks

• ###### 1. Re: Calculating IF and AND Statements with survey Data

Hi Patricia, Can you explain more about what's too restrictive and not working when you try to use AND? Something like this should work like you want:

IF [Q1 1] = 'Y' AND [Q1 2] = 'Y' AND [Q1 3] = 'Y' THEN 'Silver' END