Jurandi Frana Posted September 4, 2020 Share Posted September 4, 2020 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 Link to comment Share on other sites More sharing options...
Fabian Duerr Posted September 5, 2020 Share Posted September 5, 2020 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 Link to comment Share on other sites More sharing options...
Jurandi Frana Posted September 9, 2020 Author Share Posted September 9, 2020 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] Link to comment Share on other sites More sharing options...
Fabian Duerr Posted September 9, 2020 Share Posted September 9, 2020 Please check this approach for a very similar question: https://community.spotfire.com/questions/how-create-calculated-column-another-data-table-using-script Link to comment Share on other sites More sharing options...
Gaia Paolini Posted September 10, 2020 Share Posted September 10, 2020 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) Link to comment Share on other sites More sharing options...
Jurandi Frana Posted September 10, 2020 Author Share Posted September 10, 2020 [sTART] and [end] are contract duration, [ref] is the checkpoint. I need to check all current contracts for each checkpoint Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now