3 Replies Latest reply on Nov 7, 2018 4:04 PM by Michael Ye

# IF then AVG formula not working

Hello,

Can someone please help me with why my formula below brings in no values? It shows that the calculation is valid but when I bring in the field to a chart, no values show up, just blank. Thank you!

IF  attr([Job Status]) = "Filled" THEN AVG([Days since Open Date]) END

Below is the sample raw data.

Job Status
Days since Open Date
Closed4
Closed4

Filled

5
Filled7
Filled8
Filled4
Filled2
• ###### 1. Re: IF then AVG formula not working

Jennifer,

The calculation should be:

AVG(IF [Job Status] = "Filled" THEN ([Days since Open Date]) END)

Put in the view, the value is 5.2. It's correct, since 26/5=5.2

Hope it helps.

Michael Ye

7 of 7 people found this helpful
• ###### 2. Re: IF then AVG formula not working

Hi Michael,

Thank you! That helped.

Do you mind explaining to me why my formula didn't work and yours did? I feel like logically my formula was correct.

• ###### 3. Re: IF then AVG formula not working

Jennifer,

Lets analyse this two clauses:

Yours:  IF  attr([Job Status]) = "Filled" THEN AVG([Days since Open Date]) END

Mine:  AVG(IF [Job Status] = "Filled" THEN ([Days since Open Date]) END)

In yours, overall, it is a logical function. Importantly, it only tells how to handle "filled", it did not tell how to do with the measure if job status is not "filled", So tableau does not know how to do this if job status includes both "filled" and "closed".

In mine, overall, it is not logical function. It is an aggregate calculation (logical function is embedded in it). It simply gives the aggregate data when IF logical function is met.

Please remember. If you use logical function, you need to drag the dimension to the view and satisfy the condition (here, filter out "Closed"). Otherwise, the measure does not show.

So, in your calculation, if you drag job status to the row and filter it only for "filled", you also get 5.2, correct answer.

Hope it helps.

Michael Ye

5 of 5 people found this helpful