If you have the need to visualise empty data on a line chart - for example, if sales in one month was zero, so there's no data for that month, yet you need to show zero for that month, then you need to add a row to your data showing zero sales for that month.
Introduction
If you have the need to visualize empty data on a line chart - for example, if sales in one month was zero, so there's no data for that month, yet you need to show zero for that month, then you need to add a row to your data showing zero sales for that month.
Add Rows With Zero
The below script can be used to create rows with zeros for a certain column. It works by creating a new data table in memory and adding the rows to the original data table.
# Copyright © 2017. TIBCO Software Inc. Licensed under TIBCO BSD-style license. #Author - Andrew Berridge, TIBCO Software, September 2016 import System import Spotfire.Dxp.Application from Spotfire.Dxp.Data import * from Spotfire.Dxp.Application.Visuals import VisualContent from System.Collections.Generic import HashSet from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter import System.String from Spotfire.Dxp.Data.Import import TextDataReaderSettings from Spotfire.Dxp.Data.Import import TextFileDataSource from Spotfire.Dxp.Data.Import import DataTableDataSource from System import DateTime print DateTime.Today 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,Attritionrn") 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() + ",0rn") # 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)
License: TIBCO BSD-Style License
Recommended Comments
There are no comments to display.