1 Reply Latest reply on Aug 8, 2012 2:25 PM by Rebecca Carr

    Dealing with missing data in Table Calculations

    Rebecca Carr

      Hello,

      I have a data set of average salaries by institution over time and need to calculate the year-to-year difference in both dollar value and percentage.  When there are no gaps in an institution's data, the Table Calculations of Difference and Percent Difference work perfectly, but they don't work at all  when an institution has gaps.

       

      I've fiddled with the data extract to make the numbers meaningless, but the attached file shows an example of the problem. I would like the final result for the table calculations to: (1) show null value when the 'prev' value is missing and (2) have the value after a gap be compared to the value for 'prev - 1' instead of 'prev'. 

       

      In other words, in a crosstab of year by institution the cells with values in them would be identical for Average Salary, Dollar Increase, and Percent Increase (except that all values for "first" year would be missing on those with Table Calculations). 

       

      I hope this makes sense. I've just started on this Viz and I'm stuck until I figure out how to handle the missing data.  Any help would be very welcome!

      Thanks!

      - Rebecca

        • 1. Re: Dealing with missing data in Table Calculations
          Rebecca Carr

          I ended up figuring this out after someone at the Omaha Users Group pointed me in the right direction.  First I calculated a field for PREV YR AVG SALARY, which looks like this:

            lookup(([AVERAGE SALARY CONVERTED]), -1)

           

          and then used it in a new field called PERCENT CHANGE AVG SALARY, which looks like this:

            if (isnull([PREV YR AVG SALARY]))

            then null

            else (ZN([AVERAGE SALARY CONVERTED]) - LOOKUP(ZN([AVERAGE SALARY CONVERTED]), -1)) /

            ABS(LOOKUP(ZN([AVERAGE SALARY CONVERTED]), -1))

            end

           

          and finally used the second field in my viz.  With one minor quirk that I can't figure out (and decided to work around), this works perfectly.