Jump to content
  • Show missing data as 0 and line drop - Line Chart


    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.


    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.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.Add(dataTableName, fs)
        # Create new data table with a row for every month
        stream = MemoryStream()
        csvWriter = StreamWriter(stream, Encoding.UTF8)
        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
    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.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:




    User Feedback

    Recommended Comments

    There are no comments to display.

  • Create New...