Count dates in all range

[Jurandi Frana]

How to count all records where the reference date is in the range [start; end] for all [start; end]

 

Table entry:

 

 

 

[start]

[end]

[ref]

 

 

 

 

05/01/2020

06/01/2020

05/20/2020

 

 

05/01/2020

07/01/2020

12/01/2020

 

 

12/20/2020

12/25/2020

06/10/2020

 

 

 

 

Out:

 

 

 

 

[start]

[end]

[ref]

[count]

 

 

 

 

05/01/2020

06/01/2020

05/20/2020

2

 

 

05/01/2020

07/01/2020

12/01/2020

0

 

 

12/20/2020

12/25/2020

06/10/2020

1

 

 

 

 

example:

[ ref]05/20/2020 is in [05/01/2020; 06/01/2020] and [05/01/2020;07/01/2020] , count 2

[Fabian Duerr]

I don't see how this could work witha calculated column in a table like this. Because you want to check the value in ref column of row 1 against all other rows.

I suggest that you make one table with start and end, then cross join a table with your ref column. Then make a simple calculated column that returns 1 if your ref date is between start and end date. And then summarize your data with a cross table.

Another possibility is to write a data function that takes each ref value and then loops thru start/end date.

Let me know when you have further questions

[Jurandi Frana]

I don't know how to make loops work within the spotfire --

Cross join it's a great idea but it can be very slow on a large data set.

[/code]

[Fabian Duerr]

Please check this approach for a very similar question:

 

https://community.spotfire.com/questions/how-create-calculated-column-another-data-table-using-script

 

 

[Gaia Paolini]

where does the reference date live It is not clear from the question. Is it part of the table (if so, how can it be identified)

[Jurandi Frana]

 

 

[sTART] and [end] are contract duration, [ref] is the checkpoint. I need to check all current contracts for each checkpoint