Hello, I would like to visualize the number of documents in a particular state by date.
My documents have daily changes to their states and I have a log table with the start date & end date (null if this is the current state).
The table structure is this:
| fileId | state | start date | end date |
| 1000 | -1 | STARTDATE | 01/01/2020 08:29 |
| 1000 | 0 | 01/01/2020 08:29 | 03/01/2020 12:15 |
| 1000 | 1 | 03/01/2020 12:15 | 06/01/2020 14:17 |
| 1000 | 2 | 06/01/2020 14:17 | 07/01/2020 10:44 |
| 1000 | 3 | 07/01/2020 10:44 | NULL |
| 2000 | -1 | STARTDATE | 01/01/2020 11:07 |
| 2000 | 0 | 01/01/2020 11:07 | 02/01/2020 17:13 |
| 2000 | 1 | 02/01/2020 17:13 | 04/01/2020 14:17 |
| 2000 | 2 | 04/01/2020 14:17 | 05/01/2020 10:44 |
| 2000 | 3 | 05/01/2020 10:44 | NULL |
I would like to write a measure that counts all the files that were in a particular state, for each date value.
I will filter state=1 (for example) and I would like a chart which shows the number of files in state 1 at 01/01/2020, 02/01/2020, ... and so on. I need to visualize the time trend of the various states.
Can I write a measure or it's better to calculate the scalar product of document states and calendar dates ?
In SQL I would join calendar table with document state table ON calendar.date between documentState.startDate AND ISNULL(documentState.endDate, GETDATE()).
Can you help ?
