7 Replies Latest reply on Oct 1, 2018 7:50 AM by Anthony Sardain

    Treating NULL as zero in quick table difference calculation

    Anthony Sardain

      Hi all,

       

      I'm trying to compare the number of armed conflict events between weeks for different regions. My issue is that in some cases there are no events in a given region on a given week, but there are some on the next (or previous) week. My issue is that when one of the values is NULL that the calculation also yields NULL. I would like NULLs treated as zeroes. Hence: NULL - 7 = -7

       

      The attached file documents the difference in the number of events in the different regions between the two weeks (with the first week hidden from view). Looking at Nigeria, for instance, we see a lot of NULL values (all of which show up blue) – as shown by no number showing up in the tool-tip mouseover.

       

      Any idea what I can do to resolve this?

        • 1. Re: Treating NULL as zero in quick table difference calculation
          Joe Oppelt

          You'll probably need to write your own table calc rather than do a quick table calc.  You can use the ZN() function to force zeros there.

           

          I'll take a look.

          • 2. Re: Treating NULL as zero in quick table difference calculation
            Joe Oppelt

            I can't figure out what you have in there.


            For starters, I don't see where in the sheet's table you have anything for last week.  I made a text version of your sheet.  Nothing shows up for last week.  What did you do to exclude last week data?

             

            Look at Sheet 1.  Here I have last and this week.  The calc works as expected because last week is there.

             

            (By the way, Tableau's quick table calc DID insert the ZN() for you, so that part is OK.  It's really a problem with getting Tableau to look at last week data for the same country/admin.)

            1 of 1 people found this helpful
            • 3. Re: Treating NULL as zero in quick table difference calculation
              Anthony Sardain

              Hi Joe,

               

              You can see "Last Week" by right clicking on the "This Week" pill and clicking "Show Hidden Values".

               

              In any case I can see that Sheet 1 indeed displays the difference calculation as it should be –  how can I translate that to the map format like I had before?

               

              Thanks for your help.

              • 4. Re: Treating NULL as zero in quick table difference calculation
                Joe Oppelt

                In the attached I have made two calcs:  [Last week] does the lookup for last week.

                 

                I discovered that the ZN stuff the tableau calc is doing has the ZN inside the LOOKUP.  My calc has moved it outside the lookup.

                 

                (BTW, you can see the syntax that Tableau is doing in the quick table calc by opening a new calc editor and dragging the quick table calc pill into the editor.)

                 

                Look at the tooltips here.  I have displayed the quick table calc.  (Actually you had that there.)  I display my table calc ([Calculation1]).  I display the ZN of sum(number of records).  I display last week using my calc.

                 

                Look at the table calc settings of my two calc.  (Both are set the same.)  For the record I tossed out the [Country (copy)] calc from the sheet.  You don't need that (at least in this example.)

                 

                I have told my calcs to evaluate along Country, Admin, and Thisweek (in that order), and to restart with every Admin.  (And if you have multiple countries on the sheet, it will still work because of the order of evaluation.)  To rearrange the order of evaluation, when you are in the edit-table-calc dialog box, select "Specific Dimensions" and then drag dimensions in the list to whatever order you need.

                 

                We have to mess with specific dimensions like this because they are on the sheet but not on the columns or rows shelf.  So there is no order context  to accommodate them with the standard TABLE(across) or TABLE(down).

                 

                PS:  You showed me something.  I've been doing Tableau for years but I never used "hide" that way.  if I want to take something off the viz but leave it on the sheet, I make my own LOOKUP calc and use that as a filter.  LOOKUP (or any table calc) used as a filter controls what part of the table gets displayed without deleting actual rows from the table itself.  In the attached workbook I have made copy (3) of your original.  I did unhide, and I added the kind of filter I would have used for that.  (I displayed the filter so you can see what that looks like.  You can do that for any dimension, and a whole lot more.)

                1 of 1 people found this helpful
                • 5. Re: Treating NULL as zero in quick table difference calculation
                  Anthony Sardain

                  Hi Joe,

                   

                  This is almost perfect. The one issue that remains is when this week is NULL and last week is not null. In such cases – e.g. Orientale in the Democratic Republic of the Congo (top right of the map) – the region doesn't show up.

                   

                  Would this also be solved with a LookUp table?

                   

                  Thanks for all your help Joe.

                  • 6. Re: Treating NULL as zero in quick table difference calculation
                    Joe Oppelt

                    I couldn't get Orientale to display, no matter what I tried (in the current setup.)  There just isn't a mark for Thisweek-1 in that [Admin].  I get a mark for it on SHeet1 because [Admin] is on ROWS, and that forced a cell there.  But you can't do that with the map.

                     

                    So I took a totally different approach.  I made a [This week rows] and [Last Week rows] calc using FIXED LOD.  These give us sums (or zero) across the whole Country/Admin chunk of data, and that allows me to take [Thisweek] off the sheet entirely.


                    See version (4) of the sheet in the attached.  You might have to rework the viz-in-tooltip to use these new calcs.  (Maybe not.)

                    1 of 1 people found this helpful
                    • 7. Re: Treating NULL as zero in quick table difference calculation
                      Anthony Sardain

                      Hi Joe,

                      Sorry for the delay. The solution you provided works perfectly. Thanks so much for all your help.