Max Employees on Schedule

[Lindsay Longnecker]

I have a scatter plot that graphs all employees on the X-axis and their associated start and end times on the Y-axis. I would like to add a horizontal line at the time on the Y-axis that the max employees are working and is lablelled with that number of employees. Is this possible using a custom expression in Lines & Curves

[Shashank Gurnalkar]

I tried with the following data set:

 

Use the following IronPython Script (Considering 12 AM = 0 in the array)

from Spotfire.Dxp.Data import *

 

tableName='Data Table'

columnToFetch='Start Time'

columnToFetch2='End Time'

activeTable=Document.Data.Tables[tableName]

rowCount = activeTable.RowCount

arr = []

rowsToInclude = IndexSet(rowCount,True)

cursor1 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch])

cursor2 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch2])

 

for row in activeTable.GetRows(rowsToInclude,cursor1,cursor2):

rowIndex = row.Index

temp = [0]*24

 

val1 = int(cursor1.CurrentValue)

val2 = int(cursor2.CurrentValue)

 

for i in range(val1,val2):

temp=1

arr.append(temp)

 

output_=[sum(i) for i in zip(*arr)] #gives sum of column values

 

Document.Properties["maxEmployee"] = output_.index(max(output_))The Value of output_=[0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 3, 7, 7, 9, 10, 11, 11, 13, 12, 10, 8, 4, 3,0]

Max value is at 17th index, so max employees would be at 5 PM.

And used the following custom expression in the Horizontal line

${maxEmployee}Final result: