8 Replies Latest reply on Jan 1, 2013 9:52 AM by Mark Bradbourne

    Handling Calculation Involving Missing Values


      Hi all,


      I know there are already lots of discussions regarding handling missing/null values in graph, but I didn't see any posts of making calculated field with missing values...


      A WB is attached to describe our problem. Let's say we have actual sales data from MS SQL on product level and plan sales data from Teradata on region level (just wanted to  emphasize that we have totally different data sources so it doesn't seem possible to write a sql join within or outside Tableau to convert missing values to nulls). And these two data sources are uneven, for instance, we may not have actual sales for region S but a plan number is allocated for that region, to make sure all data is displayed as report consumer expects, I used an excel with a complete product reporting hierarchy as my primary data source and blend with the metrics from actual and plan data sources.


      Now we need to add one more calc field representing the difference b/w plan and actual (on product level), apparently we are missing this metric for region S as there is no sales for that region in system and the grand total of this difference are heading to a wrong direction because of it, report shows we beat the plan for $160 in total but actually we miss $140 as the region S plan is not rolled up in grand total at all... Is there anyway we can force the missing value act as 0 in filed calcs? I am using Tableau 7.0