Show missing data as 0 and line drop - Line Chart

Introduction

We a have line chart set up which shows the trend of attrition rate across months. If you look at the attached image you would see that we had 4% attrition in January 2015 after that we had 4% again in August however it is not clear from the visualization ? I want to show 0's (Line drop whenever it's 0) for months when we did not have attrition (as it is a positive point to highlight). The tricky thing with my data is that it only has information on months in which we had attrition.

Is there any way through which I could show the line drop + 0 value for the month in which we did not have any attrition? Again, this information is not available in my data table I am just trying to also highlight no data with 0 value and line drop.

Solution:

The challenge is that missing data has no meaning, so Spotfire can't infer that there's missing data because there's nothing to tell it what's going on. 

You need to supply Spotfire with a row for every month with zero for that month. You can do it by creating a data table with a row for every month and adding those rows to your main data table. You can create the data table in IronPython and add the rows to your existing data table. Here's a script that does that.

#Author - Andrew Berridge, Copyright Spotfire Software, September 2016. Licensed under TIBCO BSD-style license.
 
from Spotfire.Dxp.Data import *
from System.IO import MemoryStream, SeekOrigin, StreamWriter
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data.Import import DataTableDataSource

def LoadCSV(dataTableName, stream):
    settings = TextDataReaderSettings()
    settings.Separator = ","
    settings.AddColumnNameRow(0)
    settings.ClearDataTypes(False)
    settings.SetDataType(0, DataType.Date)
    settings.SetDataType(1, DataType.Integer)
    
    stream.Seek(0, SeekOrigin.Begin)
    
    fs = TextFileDataSource(stream, settings)
    
    if Document.Data.Tables.Contains(dataTableName):
        Document.Data.Tables[dataTableName].ReplaceData(fs)
    else:
        Document.Data.Tables.Add(dataTableName, fs)
         
    # Create new data table with a row for every month
    stream = MemoryStream()
    csvWriter = StreamWriter(stream, Encoding.UTF8)
    csvWriter.WriteLine("Date,Attrition\r\n")
    
    end = DateTime.Today
    start = DateTime(2012,01,01) # Change this for your start date
    currentDate = start
     
    while currentDate < end:
        print (currentDate)
        currentDate = currentDate.AddMonths(1)
        csvWriter.WriteLine(currentDate.ToLongDateString() + ",0\r\n")
        
# Now load the generated data
csvWriter.Flush()
LoadCSV("Rows with Zero", stream)
     
# The original data table
table = Document.Data.Tables["Attrition"]
# Add Rows
ds = DataTableDataSource(Document.Data.Tables["Rows with Zero"])
addRowsSettings = AddRowsSettings(table, ds, "Is Zero Row", "Yes", "No")
# Now remove existing Zero Rows and Is Zero Row column
if (table.Columns.Contains("Is Zero Row")):
    existingZeroRows = table.Select('[is Zero Row] = "Yes"')
    table.RemoveRows(existingZeroRows)
    table.Columns.Remove("Is Zero Row")
# Do the work of adding rows:
table.AddRows(ds, addRowsSettings)

The image shows the problem - 2016 is missing data from Feb-Jul for 2015: