7 Replies Latest reply on Apr 18, 2013 6:55 AM by Ben Price

IF function - Am I doing it wrong?

Hi there,

I'm currently analysing a pretty large data set from a survey that we have been running for the last year.

My trouble is that I can't seem to make a simple IF calculation work.

I've attached a screenshot of our set-up - ALL questions are in one variable in columns and ALL answers are in another variable in rows.

To get the attached, I've filtered by the question which then only shows the answers corresponding to that question.

Our values are made up of a COUNTD of the unique Respondent ID (Arguably not the best way of doing it)

Now what I'm trying to calculate is the average budget from this chart. - I started by trying the following IF function:

This would then give a number value to the total cost in each bracket - but it keeps returning the error "Cannot mix aggregate and non-aggregate comparisons or results in "IF" expressions".

If I can get the above to work then that would be a start and I add some average functions to give me my total.

There may also be a better way of structuring the data, so I've attached a snapshot of the excel that I'm working from too.

Many Thanks,

• 1. Re: IF function - Am I doing it wrong?

Andrew,

The condition in your if statement is at the row-level and your outcome is at the table-level.  This is why Tableau doesn't like it.  Try

COUNTD(

) * 5000

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• 2. Re: IF function - Am I doing it wrong?

Thanks alot for the help, that seems to work.

However, how would I nest that? for example, I have 6 different arguments that I would like to calculate in one go - £5k, £30k, £75k, £175k, £375k and £500k. Therefore, I can't nest them all under one count as they will all be multiplied by the same number...

Also, as you were so quick on it, do you have any suggestions on calculating the average from all of those?

Many Thanks

• 3. Re: IF function - Am I doing it wrong?

Ben,

You should be able to add all of them together as follows:

COUNTD(

) * 5000 +

COUNTD(

) * 30000 + ...

However, this is not optimal.  I wish I had the data to play with.  There are quite a few ways to do this better, but they would depend on how you plan on visualizing the data.  Could you post a mock-up or .twbx?

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• 4. Re: IF function - Am I doing it wrong?

There should be a sample of the data above, can you work with that? - All you have to do is connect it to a workbook and it should pull it in as I have it here?

• 5. Re: IF function - Am I doing it wrong?

I have managed to find a workaround, although it's less than optimal, so I think there may be issues with the way I have set-up the data.

What I've done is duplicated that calculation for all 6 variables then added in another calculation that aggregates all the previous calculations:

([£5k]+[£30k]+[£75k]+[£175k]+[£375]+[£500k])/COUNTD([RespondentID])

Is this the best way or is there another one that I'm missing?

• 6. Re: IF function - Am I doing it wrong?

Ben,

Take a look at this mock-up.  It utilizes that "more efficient" approach that I mentioned earlier.  Does this help?

Associate Consultant

Mariner, LLC