7 Replies Latest reply on Jun 21, 2016 11:24 PM by Ashish Chaudhari

    % calculated field help...?

    Steve Gesuale

      Hi Tableau experts,

       

      Trying to kill two calculated field birds with one stone… Dealing with data with a lot of NULLs.

       

      1. How can I revise the 11 - % calc <sum([11  N]) / SUM ([11  D])> to exclude the numerator if the denominator is NULL and vice versa? In other words, using the data below, the SUM of [11 N] would be 1,153 (Guinea's 1,173 would be excluded) and the SUM of [11 D] would be 1,536 (Kenya's 500 would be excluded) and then the % calc in Sheet 2 would return 99% instead of 159%

      Capture.JPG

       

      2) How can I revise the below calc (# of countries reporting) so that it includes only countries that reported both 11 N and 11 D and ignores the NULLs? So the calc in Sheet 2 would return 5 instead of 16?

       

      { FIXED [Country]: COUNTD([Number of Records]) }

       

      Thanks for any help!

      Steve

        • 1. Re: % calculated field help...?
          G Marc Turner

          Hi Steve,

           

          Here are two possible options. The first option would to be to place [11 N] and [11 D] on the filters shelf. Use "All Values" and then make sure the box in the bottom right to "Include Null Values" is unchecked. This should fix all of the calculations. This would remove the countries that don't have both N and D from the table though.

           

           

          The second option is to use IF statements in the formula to determine if the values are present

          For #1, you could use this formula:

           

               sum(iif(isnull([11  D]) , NULL, [11  N])) /  sum(iif(isnull([11  N]) , NULL, [11  D]))

           

          Basically, it adds an IF statement to determine whether the value should be included in the calculation. This won't impact the totals you get for [11 N] and [11 D] though since the values still exist when summed to get the grand total. You would need to create a calculated field for each.

           

          For #2, you could use this formula which also is based on using an IF statement to determine whether both [11 N] and [11 D] are present before counting the record.

           

               iif(isnull([11  D]) or isnull([11  N]), 0, 1)

           

          Using the first approach based on filtering out records with NULL values is probably the easier approach since it removes the records from all the calculations so you don't have to miss something along the way. But, if you need to list all the countries, then the formula approach might be a better option.

           

           

          Hope this helps

          -Marc

          1 of 1 people found this helpful
          • 2. Re: % calculated field help...?
            Ashish Chaudhari

            Hi Steve,

             

            I have created simple calculation field and added it to the filter and selected in "True". Please refer to the screenshot for the results.

             

            Here is the code for the same.

             

            [11  D]>0 and [11  N]>0

             

            Output on sheet1

             

            Output on sheet2

             

            On Dashboard

             

             

            I hope this helps. Unable to upload the file due to net problem.

             

            Thanks and Regards,

            Ashish Chaudhari

            1 of 1 people found this helpful
            • 3. Re: % calculated field help...?
              Ashish Chaudhari

              Hi Steve,

               

              Please find the attached for the same.

               

              -Ashish

              1 of 1 people found this helpful
              • 4. Re: % calculated field help...?
                Steve Gesuale

                Hi Marc and Ashish,

                 

                Thanks for your responses and explanations - very helpful! Really appreciate the clarifications.

                 

                I've run into a bit of a snag when going back to the raw data. It's more granular with a Date field that I suspect is causing problems. I tried an LOD calc based on the one Ashish added (in the attached workbook), but it doesn't work. I should have used the raw data to begin with...

                 

                Any continued advice would be great!

                 

                Thanks.
                Steve

                • 5. Re: % calculated field help...?
                  Ashish Chaudhari

                  Hi Steve Gesuale,

                   

                  Updated calculations for Calc_Filter. Put this in the filter section and select True. Earlier calculation was at each row (dis-aggregated).

                   

                  SUM([11 _D])>0 and SUM([11 _N])>0

                   

                   

                  This will give you desired result.

                   

                  -Thanks and Regards,

                  Ashish Chaudhari

                  1 of 1 people found this helpful
                  • 6. Re: % calculated field help...?
                    Steve Gesuale

                    Thanks again, Ashish! That does it and I understand the difference so learned something from this. Appreciate the help.
                    Steve

                    • 7. Re: % calculated field help...?
                      Ashish Chaudhari

                      You are welcome Steve.