2 Replies Latest reply on Dec 7, 2016 11:03 AM by Cheri Beckman

    Formula works in Crosstab but not Pie

    Cheri Beckman

      I'm trying to show total audits by type of audit (2 types).  Have created the formulas and all looks right in the crosstab.  However, when I put it into a pie chart, some of the formulas return 0 in some cases.  I will attach workbook, and appreciate any help!

       

        • 1. Re: Formula works in Crosstab but not Pie
          Michel Caissie

          Cheri,

           

          instead of using a function like

          ZN(IF ATTR([Dim Audit Type Key]) = 1 THEN COUNTD([WO Audit]) END)

           

          go for

          ZN(COUNTD(IF [Dim Audit Type Key] = 1 THEN [WO Audit] END))

           

          This way you avoid using the ATTR() function, which is the cause of the 0s.

           

          If you look on the Grand Total in your crosstab, you also have 0  because Tableau don't take into account the Dim Audit Type Key level of detail when computing the total. And since ATTR([Dim Audit Type Key])  returns null because there is not a single value of Dim Audit Type Key in the view , the if condition is false and no countd is computed.

           

          The same thing happens in the Pie Chart where you don't have  Dim Audit Type Key in the view.

          Using the corrected formulas , the if logic is applied individually on each row, then the result is aggregated according to the level detail of the view.

           

          Michel

          • 2. Re: Formula works in Crosstab but not Pie
            Cheri Beckman

            Woohoo!! Thanks so much, this works perfectly.

            Whew!