2 Replies Latest reply on Nov 28, 2012 7:17 AM by Glenn H

    Table Calc Percent Difference giving wrong values

    Glenn H

      I am running a table calc of percent difference on a number of values.  Whenever there is a null in the previous year and a null in the current year, it gives me a percent difference of -100%. 

       

      Is there a way to modify the table calc to ignore value(I could have sworn it is written to do so by using ZN function) from the advanced tab in the edit table calc location?

       

      I have attached a screenshot of the values and here is the calculation from Tableau.

       

      (ZN(COUNT([CaseOffenses_Section])) - LOOKUP(ZN(COUNT([CaseOffenses_Section])), -1)) / ABS(LOOKUP(ZN(COUNT([CaseOffenses_Section])), -1))

        • 1. Re: Table Calc Percent Difference giving wrong values
          Dimitri.B

          From what I can see in the screenshot, you have a bigger problem than null to null. Change from 3 to 1 is not a drop of 50%, change from 1 to 2 is not nothing, change from 6 to 2 is not a drop of 50%, etc.

          Looks like something is going on with the calculation, most likely the partitioning of it. Without a sample workbook it would be hard to figure out what's wrong.

          1 of 1 people found this helpful
          • 2. Re: Table Calc Percent Difference giving wrong values
            Glenn H

            I had been looking at the screen too long and completely missed that. The issue is coming from a hidden week. The percent difference is calculating across the table and is including the rows that are hidden.

             

            I hid that week because I need to see not the previous week but two weeks previous for 2011 and 2012.  I had completely forgotten I did that when I posted the question.

             

            With that bit of info, I was able to create a calculated field to take care of the date so I no longer have to hide the field.

            if DATEPART('week',[Report Date]) = (DATEPART('week', NOW())-1)

            THEN [Report Date]

            ELSE

            NULL

            END

             

            Thanks Dimitri.