How would you write a custom expression for a bar chart to bin items spanning a period of time

[Ray Jorgensen]

I'm wrestling with how to write a customexpression that would span a time period and count objects that are "active" during that period. The bar chart would have binbydate showing months, with the Y-Axis showing counts colored by the active status during a given month for the object. Here's an example data set:

Start Date End Date State

15-Jan 21-Apr Open

14-Feb 31-Mar in Review

22-Mar 1-Jun Open

 

The result would be a bar chart as follows:

Jan 1 Open

Feb 1 open, 1 In Review

Mar 2 Open, 1 in review

Apr 1 Open

The key is counting the number in each state at the end of a given month.

In MS Excel, I can use COUNTIFS(, Start Date < "end of month", End Date >= "end of month") (counts items still active by the end of the month;if closed within the month, it's no longer active.)

I've tried several variants with Spotfire, but keep getting error messages. My most promising approach was "UniqueCount( If( DateDiff( [start Date] ,[Axis.X]) < 0 , [RowId], null))", but that didn't work. I'm not sure how to compare a column date to the MAX([Axis.X]) (yes, I tried that, too).

Thanks for any advice that you may have!

[Ray Jorgensen]

After soliciting some advice from peers and experimenting with a possible approach, I've figure out a way to tackle this topic, however not with a custom expression, but rather withsome new calculated columns and a major transformation of the dataset.

To identify the "active" state of an artifact between the startand finish dates, I added calculated columns for each month of a year (Jan-Dec) with the following equation for each column:

IF([start Date]

[Ray Jorgensen]

Move to "Comments" in other response.

[Ray Jorgensen]

Move to "Comment" in other response.

[Ray Jorgensen]

In addition, I was attempting to indicate the "enduring state" of a task.  For example, on the "end date", the task transitioned to closed, and thus remains "closed" until acted upon by another event.  The transition to the "closed" state is recorded, but how do you capture the fact that the task remains closed in subsequent months.  To assign this task, I created 12 end state columns (Jan - Dec End State) to capture the enduring nature of this state with the following equation.

 

IF([End State] Is Not Null,IF([End Date]<=(If(Month(Today())<1,Date(Year(Today()) - 1,1,31),Date(Year(Today()),1,31))),
 If(Month(Today())<1,Date(Year(Today()) - 1,1,31),Date(Year(Today()),1,31)),null),null)

 

This logic assigns the last day of the month to the "Monthly End State" column to indicate that the end state is still active in each subsequent month.

 

The resulting table based on my original example above would be as follows:

 

Start Date

End Date

Begin State

End State

Jan Activity

Feb Activity

Mar Activity

Apr Activity

May Activity

Jun Activity

Jan End State

Feb End State

Mar End State

Apr End State

May End State

Jun End State

15-Jan

21-Apr

Open

Ready for Review

15-Jan

28-Feb

31-Mar

30-Apr

31-May

30-Jun

14-Feb

31-Mar

In Review

Closed

14-Feb

31-Mar

30-Apr

31-May

30-Jun

22-Mar

01-Jun

Open

Reviewed

22-Mar

30-Apr

31-May

30-Jun

 

 

 

In the next post, I'll address the final transformation. 

[Ray Jorgensen]

Lastly, I used the "Unpivot" transformation to realign the new "monthly columns" into a single column.

 

Start Date	End Date	Begin State	End State	Period Type	Active Period
15-Jan	21-Apr	Open	Ready for Review	Jan Activity	15-Jan
15-Jan	21-Apr	Open	Ready for Review	Feb Activity	28-Feb
15-Jan	21-Apr	Open	Ready for Review	Mar Activity	31-Mar
15-Jan	21-Apr	Open	Ready for Review	Apr End State	30-Apr
15-Jan	21-Apr	Open	Ready for Review	May End State	31-May
15-Jan	21-Apr	Open	Ready for Review	Jun End State	30-Jun
14-Feb	31-Mar	In Review	Closed	Feb Activity	14-Feb
14-Feb	31-Mar	In Review	Closed	Mar Activity	31-Mar
14-Feb	31-Mar	In Review	Closed	Apr End State	30-Apr
14-Feb	31-Mar	In Review	Closed	May End State	31-May
14-Feb	31-Mar	In Review	Closed	Jun End State	30-Jun
22-Mar	01-Jun	Open	Reviewed	Mar Activity	22-Mar
22-Mar	01-Jun	Open	Reviewed	Apr Activity	30-Apr
22-Mar	01-Jun	Open	Reviewed	May Activity	31-May
22-Mar	01-Jun	Open	Reviewed	Jun End State	30-Jun