I have an start date and end date. How do I get the sum of how many were in progress each month

[Josh Petersheim 2]

I have a dataset with unique rows for each entity. Each row has a start date and an end date.

I need to track how many were open at each time point. It seems like a simple problem but I have been unable to solve the problem using Spotfire calculations.

 

The data looks something like this (but thousands of rows from 2013-2022).

Entity

Start

End

1

Jan-22

Apr-22

2

Jan-22

Jan-22

3

Jan-22

Feb-22

4

Jan-22

Feb-22

5

Jan-22

Apr-22

6

Feb-22

Feb-22

7

Feb-22

Feb-22

8

Feb-22

Apr-22

9

Feb-22

Mar-22

10

Feb-22

Mar-22

11

Feb-22

Apr-22

12

Mar-22

Mar-22

13

Mar-22

Apr-22

14

Mar-22

Mar-22

15

Mar-22

 

16

Mar-22

Apr-22

17

Mar-22

Apr-22

18

Apr-22

Apr-22

19

Apr-22

 

20

Apr-22

 

 

I need to show the following in a line graph, bar graph, or cross table (calculated manually):

 

Number In Progress During the Month

Jan-22

5

Feb-22

6

Mar-22

11

Apr-22

11

 

I think the calculation needs to involve something similar to "sum(if start_date >= axis.x and finished_date =< axis.x, 1,0))" but I can't figure out how to use Axis.X in the bar/line chart or the row identifier in the cross table.

 

Any thoughts are appreciated.

 

Thanks,

Josh

[Jose Leviaguirre]

Hello Joshua,

 

I saw 12 items in progress during the month of march (1,5,8-17). I think in your calculation you have to consider null values by replacing them using the SN function with the current of future date. Something like this:

 

If((Month([start])=Axis.x),1,0)

 

The only way I can think of is by creating a calculated column with the above expression per each month. The tricky part is when you have different years, for example (Dec-21, Feb-22)

[Fabian Duerr]

Create a second table that has a column with the full range of dates and a dummy ID column with a single value or string. Then create the same dummy ID column in your original table with the same single value or string. Now, you can join your original table to the second table on the ID column. This might result in a huge table. But now you can compare your start and end days against any date and perform the counting: If([start] = [Date], 1, 0)

Currently your date columns seem to be strings. I suggest to use dates instead: Mar-22 -> 03/01/2022. Thus, also the second table should have dates like01/01/2013,02/01/2013,03/01/2013, .... ,03/01/2022,04/01/2022. Ten years with 12 months each will create 120 rows. Mutliplied with 10k rows of your data set, the resulting table might have about 1 million rows. This should still work fine.

[Josh Petersheim 2]

Hey Jose,

 

Thanks for the input. It seems this would result in me having a calendar table which I am trying to avoid if possible. I am hoping there is a way to calculate it based on the binned-date X axis that I am unaware of.