how to count of issue with open status in spotfire

Question

I need to calculate count of issue ID for each month with open status. I have below 3 columns-

Issue_ID
Issue_Open_Date
Issue_Closed_Date

Issue_ID    Issue_Open_Date Issue_Closed_Date   Open_Issue_Count(required output)
IS_10   11/11/2014  1/5/2015    3
IS_11   11/12/2014  12/14/2014  
IS_12   11/13/2014  11/15/2014  
IS_13   11/14/2014  3/5/2015    
IS_1    12/1/2014   12/15/2014  4
IS_2    12/2/2014   2/10/2015   
IS_3    12/3/2014   1/15/2015   
IS_4    1/1/2015    2/10/2015   4
IS_5    1/2/2015    3/11/2015   
IS_6    1/3/2015    1/22/2015   
IS_7    2/1/2015    3/5/2015    3
IS_8    2/2/2015    2/2/2015    
IS_9    2/7/2015    2/28/2015   
IS_14   3/1/2015    4/5/2015    1

Based on above table, i need a count of open status of each month. lets suppose in December i need to count than it should check in dec and nov month. If any issue is closing in same month, it mean that is not in open stage,

Basically for each month it should check for their records also and previous month records also. Required output is below- Nov- 3 Dec- 4 Jan-4 Feb-3 march-1

Answer 1

So... I have a way but it's ugly. I'm sure there's a better way but I spent a while banging my head on this trying to make it work just within Spotfire without resorting to a python script looping through rows and making comparisons.

With nested aggregated case statements in a Cross Table I made it work. It's a pain in the butt because it's pretty manual (have to add each month) but it will look for things that have a close date after the month given and an open date that month or earlier.

<
Sum(Case  
when ([Issue_Closed_Date]>Date(2014,11,30)) AND ([Issue_Open_Date]<Date(2014,12,1)) then 1 else 0 end) as [NOV14_OPEN] NEST 

Sum(Case  
when ([Issue_Closed_Date]>Date(2014,12,31)) AND ([Issue_Open_Date]<Date(2015,1,1)) then 1 else 0 end) as [DEC14_OPEN] NEST 

Sum(Case  
when ([Issue_Closed_Date]>Date(2015,1,31)) AND ([Issue_Open_Date]<Date(2015,2,1)) then 1 else 0 end) as [JAN15_OPEN] NEST 

Sum(Case  
when ([Issue_Closed_Date]>Date(2015,2,28)) AND ([Issue_Open_Date]<Date(2015,3,1)) then 1 else 0 end) as [FEB15_OPEN] NEST 

Sum(Case  
when ([Issue_Closed_Date]>Date(2015,3,31)) AND ([Issue_Open_Date]<Date(2015,4,1)) then 1 else 0 end) as [MAR15_OPEN]>

Screenshot:

As far as doing it with python you could probably loop through the data and do the comparisons and save it as a data table. If I'm feeling ambitious this weekend I might give it a try out of personal curiosity. I'll post here if so.

Answer 2

I think what makes this difficult is that it's not very logical to add a column showing number of issues open at a point in time because the data doesn't show time; it's "one row per unique issue."

I don't know what your end result should be, but you might be better off unpivoting the table.

1. unpivot the above data with the following settings:

   • pass through: [Issue_ID]
   • transform: [Issue_Open_Date], [Issue_Closed_Date]
   • optionally rename Category as "Action" and Value as "Action Date"

2. now that each row represents one action, create a calculated column assigning a numeric value to the action with the following formula.

   CASE [Action] WHEN "Issue_Open_Date" THEN 1 WHEN "Issue_Closed_Date" THEN -1 END

3. create a bar chart with [Action Date] along the X axis (I wouldn't drill further than month or week) and the following on the Y axis:

   Sum([Action Numeric]) over (AllPrevious([Axis.X]))

you'll wind up with something like this:

you can then do all sorts of fancy things with this data, such as show a line chart with the rate at which cases open and close (you can even plot this on a combination chart with the pictured example).