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

    Can´t calculate totals with calculated field using if conditions

    Neivam Carvalho

      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
          Joe Oppelt

          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
            Neivam Carvalho

            Hi Joe, thanks for your reply.

             

            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.

             

             

            Thanks for your attention.

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

              Right now your sheet "Q2" looks like this:

               

               

              That's the sheet that's on your dashboard.  It's doing your totals already.


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

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

                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
                  Joe Oppelt

                  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
                    Joe Oppelt

                    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
                      Neivam Carvalho

                      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
                        Joe Oppelt

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