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

    IF then AVG formula not working

    Jennifer Yoon

      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
          Michael Ye

          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

           

          1 of 1 people found this helpful
          • 2. Re: IF then AVG formula not working
            Jennifer Yoon

            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
              Michael Ye

              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