8 Replies Latest reply on Feb 22, 2017 10:42 AM by Joe Oppelt

# Can´t calculate totals with calculated field using if conditions

Hello all,

I need help with totals using if conditions on calculated fields. I´m using parameters to set the value for columns 0, 1, 2, etc.. that we call "dependentes". But, I can´t calculate the totals.

I tried to use fixed or exclude level of details, non success. Could you guys help me? On attachment follow my files and what I need is on yellow mark.

Thank you very much.

• ###### 1. Re: Can´t calculate totals with calculated field using if conditions

I'm sorry, but I can't tell from your files what you need.

I have this open.  Help me understand what is wrong with the way this is working.

• ###### 2. Re: Can´t calculate totals with calculated field using if conditions

For exemple, I have for each column below (0,1,2,3,4,5) one calculated field, that multiplies one value for the quantity of persons on variable Perfil (aprendiz, operacional, etc..)

The calculation field that I´m using on calculation is like this:

if avg([Qtde_Dependentes]) = 5 then

if [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 6 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 5 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 4 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 else

[Qtde_Titulares] END

ELSE

if avg([Qtde_Dependentes]) = 4 then

if [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 5 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 5 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 4 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 else

[Qtde_Titulares] END

ELSE

if avg([Qtde_Dependentes]) = 3 then

if  [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 4 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 4 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 4 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 ELSE

[Qtde_Titulares] END

ELSE

if avg([Qtde_Dependentes]) = 2 then

if [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 3 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 ELSE

[Qtde_Titulares] END

ELSE

if avg([Qtde_Dependentes]) = 1 then

if  [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 2 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 ELSE

[Qtde_Titulares] END

ELSE

if avg([Qtde_Dependentes]) = 0 then

if  [Seleciona_Dependentes_Cálculo] = 5 then [Qtde_Titulares] * 1 elseif

[Seleciona_Dependentes_Cálculo] = 4 then [Qtde_Titulares] * 1 elseif

[Seleciona_Dependentes_Cálculo] = 3 then [Qtde_Titulares] * 1 elseif

[Seleciona_Dependentes_Cálculo] = 2 then [Qtde_Titulares] * 1 elseif

[Seleciona_Dependentes_Cálculo] = 1 then [Qtde_Titulares] * 1 elseif

[Seleciona_Dependentes_Cálculo] = 0 then [Qtde_Titulares] * 1 ELSE

[Qtde_Titulares] END

END

END

END

END

END

END

I don´t know if this it´s OK to understand my thought.

So, I take this IF to calculate inside that table above. But totals doesn´t work. That is my problem, I need to reach some like excel below, in yellow.

• ###### 3. Re: Can´t calculate totals with calculated field using if conditions

Right now your sheet "Q2" looks like this:

Are you saying that the values in the individual cells are wrong?

• ###### 4. Re: Can´t calculate totals with calculated field using if conditions

Oh, I totally missed the "Q3" sheet.  It's scrolled off the bottom in my Desktop.

I see it now.

Hang on.

• ###### 5. Re: Can´t calculate totals with calculated field using if conditions

OK, I have it for you.

Tableau doesn't do well with totaling aggregate calcs, and [C01_ValorContribuicaoTotalLimitador] becomes an aggregate because way back in the string of calcs, [C01_Custo_Colaborador] is an aggregate when it uses the AVG() function.

LOD calcs are not aggregates, so I eliminated the use of aggregates by making that AVG() portion of the calc an LOD calc.  See [AVG Valor per Perfill].  I made a copy of [C01_Custo_Colaborador (copy)].  I replace the AVG() portion with the LOD calc (and removed the ATT() off [Perfill].)  Also, see the syntax of that calc for a better way to handle the CASE function.

On sheet "Q3" I displayed both the original [C01_Custo_Colaborador] and the new copy to show that the new copy arrives at the same value.  (Note:  LOD calcs get evaluated for all rows, so we don't want to use SUM() to display it at the [Perfill] level.  Just the AVG().)

then I looked at [C01_ValorContribuicaoTotalLimitador] and realized that it just ends up being a copy of [C01_Custo_Colaborador], so I just displayed [C01_Custo_Colaborador (copy)] on Sheet 4.  Now that it's not an aggregate calc being displayed, Tableau is doing a SUM() on that field, and all the Analysis->Totals work as expected here.

• ###### 6. Re: Can´t calculate totals with calculated field using if conditions

Now that I look between the two sheets, I think I missed a step.  [Cen01_Contribuição_Unitário] ends up being a copy of  [C01_Custo_Colaborador], but I should have run that through the [C01_ValorContribuicaoTotalLimitador] logic.  But still, that will work too because we have eliminated the aggregate way  back in [C01_ValorContribuicaoTotalLimitador].

• ###### 7. Re: Can´t calculate totals with calculated field using if conditions

Hello Joe,

Thanks a lot for your support. Your example was very clear, but I didn't see the sum for the other columns, like 1,2, etc... But you opened my mind for a solution.

Now I'll try to make some changes on it.

Thanks again!

• ###### 8. Re: Can´t calculate totals with calculated field using if conditions

Right.  the key was getting the aggregate out of there.  You can clean it up from there.