2 Replies Latest reply on Jan 7, 2019 7:19 AM by Natalia Sojka

    SUM IIF statement that deals with divide by 0 values

    Justin Holder

      Hi all,

       

      I'm having some trouble creating a calculation to counteract an issue that's causing Nulls when there shouldn't be.

       

      I have attached a workbook with a sample of the data, with filters excluding a few weeks for specific comparative reasons.

       

      The issue surrounds "Combined Metric A".  The charts are focused on weekly data for each country, however data is not present for Metric A and Metric B in all countries each week; Metric A is then Length Weighted against Metric D.  The original form for this data was in Excel and a SUMPRODUCT was used and thus the calculation is structured as below and "Combined Metric A" is a calculation to combine the sum from each country each week.  The code for this calculated field is as such:

       

      (SUM(IIF( [Country] = 'Denmark', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'Denmark', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'France', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'France', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'Italy', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'Italy', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'Poland', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'Poland', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'Portugal', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'Portugal', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'Spain', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'Spain', [Measure D],0)))

      +

      (SUM(IIF( [Country] = 'United Kingdom', [Length Weighted Metric A],0)))

      /(SUM(IIF( [Country] = 'United Kingdom', [Measure D],0)))

       

      In the attached workbook a table is shown to demonstrate the missing data for some weeks.  The sheet labelled "Line" is an attempt to construct a Line chart comparing FY17 and FY18, however much of the data is not shown due to there being Nulls, but they are not actually Nulls!

      Nulls Line.PNG

      What I've deduced is that the weeks where there is one country missing data, the above calculation appears to be doing a divide by 0 for that week and this throws off the whole calculation as a Null (correct me if I'm wrong please)!

       

      I need to correctly structure the calculation so as to ignore when a country has missing data and still give me the total for that week.

       

      This was originally achievable in Domo (-_-) and I need to be able to replicate it now in Tableau.

       

      Thanks!

        • 1. Re: SUM IIF statement that deals with divide by 0 values
          Jim Dehner

          Hi Justin

           

          There are 2 types of nulls - one where the record has a null value in a field and the other where the record is missing

          you have the second - but you knew that - so what to do

          an approach is to pad the data by creating a scaffold -

          It is not all that difficult - at TC18 there was a presentation on the technique using time series - you will need to adapt to your issue with country

          Archana Ganeshalingam and Sasha Singh at Tableau present - see the link      Solving tough time-based problems with skeleton tables and Tableau Prep - YouTube

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: SUM IIF statement that deals with divide by 0 values
            Natalia Sojka

            Hi Justin,

             

            Jim's TC link is great :-)! I wanted to chime in as I have thought about your problem in a "different" way and worked from the visualization you want to create. So maybe this helps you move forward in case your objective is to create this particular FY comparison viz. While working from the visualization I did not need the calculation that you used. - I hope I understood your objective correctly here! From my understanding what causes the null values in the workbook is the calculated field, not the missing values. I have left out the if-statement-calculated field as I only needed a calculated field in my viz and used Tableau to aggregate my values depending on the level of detail that I am choosing. Not knowing the whole metrics calculation I think there is one step missing in my approach though, namely checking that mathematically it is correct to take the average as a way of aggregating the metrics results.

             

            Hope this gives you additional ideas and helps you move forward.

             

            Natalia