7 Replies Latest reply on Apr 10, 2012 11:57 AM by Tracy Rodgers

    Refering to Null Values in Formuls

      I have got 2 Data Sources.

      The first datasource has data of Invoices Received which has a column for the Invoice Number and a column for the Amount, the sample of the Datasource is shown below.

      INV_RCVD_NO

      RCVD_AMOUNT

      A001

      100

      A002

      110

      A003

      120

      A004

      130

      A005

      140

      A006

      150

       

       

      The second datasource has data for Invoice Paid which has a column for the Invoice Number and a column for the Amount Paid as shown below.

      INV_PAID_NO

      PAID_AMOUNT

      A001

      100

      A002

      110

      A003

      120

      A004

      130

      A005

      140

       

      All the Invoices received(information in the first datasource) may not be paid (information in the second datasource). My objective here is to find the list of Invoices that have been received but not paid.

      I want to create a Cross Tab View which displays only the invoices that have been Received but not paid. I tried to create some Formulas but I am not able to refer to a Null values in my formula.

      How can I create a Formula to only display the Invoices that are not paid.

      Attached is the Workbook which I have created by blending the both the Data sources.

      As seen in the View the Invoices which are not paid appear as empty Rows in both my formulas and hence I am not able to filter these Rows.

      How can I only display the Rows which do not have any Paid_Amount?

        • 1. Re: Refering to Null Values in Formuls
          Jonathan Drummey

          Hello Ameet,

           

          Blends do some strange (i.e. I don't understand them yet) things in terms of padding rows and nulls/non nulls. In cases like this the blend doesn't seem to return anything at all that can be evaluated. The way around that is to use the LOOKUP() table calculation. LOOKUP([field],0) finds the instance of that field in the view, and returns Null if there is no value. Then we can wrap that in the ZN() function, like so:

           

          sum([RCVD_AMOUNT]) - ZN(LOOKUP(SUM([INV_PAID (Invoiced_PAID.xlsx)].[PAID_AMOUNT]),0))

           

          I called this Balance Due. Then you can put this on the Filter shelf to filter for values that are at least 1.

           

          Jonathan

          • 2. Re: Refering to Null Values in Formuls
            Tracy Rodgers

            Hi Ameet,

             

            The issue is that there are more INV_RCVD_NO than INV_PAID_NO instances. Since there is not data, for example, A006 in INV_PAID_NO Tableau cannot compare the two data sources. Using Jonathan's example manually gives a value that is not actually there. Another way of doing this is to create a calculated field that looks like the following:

             

            iif(isnull((ATTR([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO]))), 0, ((SUM([INV_PAID (Invoiced_PAID.xlsx)].[PAID_AMOUNT]))))

             

            Hope this helps!

             

            -Tracy

            • 3. Re: Refering to Null Values in Formuls
              Jonathan Drummey

              Hi Tracy,

               

              I'm curious, why would ISNULL(ATTR([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO])) return True when there's no row being returned from the blend while ISNULL(SUM([INV_PAID (Invoiced_PAID.xlsx)].[INV_PAID_NO])) returns Null?

               

              I've been wondering about this for a bit, and just submitted a support request to get an answer where I attached the following workbook. I'm used to databases that return Null when the left join doesn't return data, I'm trying to figure out what Tableau does.

               

              Jonathan

              • 4. Re: Refering to Null Values in Formuls

                Thanks Jonathan and Thanks Tracy,

                I tried out both the methods.

                When I use Jonathan method I am able to filter for the Invoices that are Not Paid and it works.

                I tried the same using Tracys Method but I am not able to filter for the Invoices that are Not Paid.

                 

                It would be helpful if Tracy can have a look at the Sheet Tracy_Formula_Testing in the attached workbook. I am trying to drag the Calculated field TEST-TRACY_CALC1 to the Filters Card and only filter the Not Paid. The field TEST-TRACY_CALC1 does not stay in the Filters Card, I am not sure why.

                 

                Thanks once again,

                Ameet

                • 5. Re: Refering to Null Values in Formuls
                  Richard Leeke

                  If you change the calculated field from discrete to continuous you will be able to add it to the filters shelf.

                   

                  I have no idea why - just one of those quirks...

                  • 6. Re: Refering to Null Values in Formuls

                    Hi Jonathan/Tracy,

                    I was building this Report for a Customer who has around 1.4 Million Rows for Invoice received and 600 Thousand Rows in the Invoice Paid. Tableau just crashes when I bring the Invoice Received Number and Received Amount as Cross Tab View. I am not able to create the Filter. Any Suggestion would be helpful.

                    • 7. Re: Refering to Null Values in Formuls
                      Tracy Rodgers

                      Hi Ameet,

                       

                      In response to the calculation not being able to be placed on the filter shelf, this is because calculated fields that are strings but use aggregated fields cannot be placed on the filter shelf. Tableau is unable to recognize them. To get around this, instead of using Paid, Not Paid use 0 and 1. So the calculation will look like the following:

                       

                      IIF((sum([RCVD_AMOUNT]) - [TEST-TRACY_CALC]) <> 0, 1, 0)

                       

                      Then, place this calculation on the level of detail shelf. Right click on it and select Continuous. Right click again and select Filter. Then, you can filter to one to show only the Not Paid members.

                       

                      I'm not sure why Tableau is crashing--you might want to send this to our Support team at:

                       

                      http://www.tableausoftware.com/support/request

                       

                      Be sure to include:

                       

                      1. Screen shot of the error message, including the 'Show Details' information (if available)

                       

                      2. Date and time of the issue

                       

                      3. Log files (see http://www.tableausoftware.com/support/knowledge-base/sending-tableau-desktop-log-files for instructions on bundling and sending the log files)

                       

                      -Tracy