2 Replies Latest reply on Apr 3, 2017 1:23 AM by Mary Solbrig

    LOD Calculation, Empty Rows, and Null Values

    Jason Hanser

      I am having a bunch of issues getting null values to show up as 0 (when the calculation uses the FIXED function) as well as preventing empty rows from being hidden. If you open the attached workbook, you should can the issues.


      1. If you use the filters to show students living Off Campus, there are a bunch of blanks in the top half of the table (the All Students section). How do you get those to show as zeros, when the calculated field using a fixed function?


      2. I have "Show Hidden Rows Selected," yet when I use the filters some rows disappear. Why is that and how do I get it to stop? (example: un-clicking On Campus filter causes "American Indian" row to disappear)


      3. How do you get blanks to show up as zeros when the entire row is blank? The ZN & Lookup function only seem to work when some of the values are blank, but not all. And, can this same fix be applied to the All Student section, which use a FIXED function.



      Thanks in advance!

        • 1. Re: LOD Calculation, Empty Rows, and Null Values
          Mary Solbrig

          1. The function "ZN" turns nulls into 0s. Caveat: it works best when done last, so create a field with formula ZN(SUM(All_Student_Count)) and drag that in place of SUM(All_Student_Count).


          2. A Hidden row is different than a row that has no data. This isn't showing a row for Lizard Students either, because there is no data for Lizard Students, why would Tableau show that? The filter "ON_CAMPUS" has been set to be a context filter, meaning that it's the first thing that happens in the pipe line. American Indian or Alaska Native students are filtered out by that, so for the rest of the analysis they don't exist any more than your Lizard Students do. If you remove the ON_CAMPUS filter from context (Right-click on the field and select Remove from Context), the row for American Indian or Alaska Native Students will appear again.


          Now, I imagine that the reason you had the filters set to context filter to begin with was that the All_Student_Count is fixed field. Instead, I'd suggest changing New_Student_Count to be ZN (SUM(IF [STU_ATTR] = "NEW" THEN 1 ELSE 0 END)), All_Student_Count to be ZN(SUM([Number of Records)). Or, use an {exclude STU_ATTR: COUNT([Number of Records])} instead.


          3. Uhhh I'll think about that.

          • 2. Re: LOD Calculation, Empty Rows, and Null Values
            Mary Solbrig

            BTW, another way to get the 0's to show up is:

            1. right-click on Measure Values and select Format.
            2. In Special Values, Enter "0"


            For 3, this is not a very satisfying answer, but for the specific issue of campus housing you could use a parameter as a filter instead:

            IF [Housing] = -1 THEN SUM([Number of Records])


            SUM(IIF([ON_CAMPUS] = [Housing], [Number of Records],0))



            Or use custom SQL as described in this post: Tableau Tip Tuesday: Show What Isn’t in the Data – VizPainter

            1 of 1 people found this helpful