SUM IIF statement that deals with divide by 0 values
Justin Holder Jan 7, 2019 5:50 AMHi 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!
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!

Nulls Sample Data.twbx 109.6 KB