Hi
I have data like below. The problem is that on the 25-03-2022 a system was shut down. And all the open contacts in system where ended. And on the same date all the open contacts from the old system was started in the new system. Therefore these contacts have two rows in the data. One of the two rows have "Date_end" on the 25-03-2022 and the other row have a "Date_start" on the 25-03-2022
I have to calculate the average number of days per contact from the data. The total number of days is just the sum of days in the data. But this sum of days I have to divide with a number of contacts where the contacts that was ended and started on the 25-03-2022 only count as 1 contact. The contacts have the same "ID-person" and as metioned both a "Date_end" and "Date_Start" on the 25-03-2022.
So for the sample data below there are 4 contacts, which is the number of contacts that the days have to be divided with.
It is not possible to divide with a count of distinct "Id-person", since the same "Id-person" can have more than one contact.
Anybody that can help (either in script or a set expression for chart)?
| Row(no) |
ID-person |
Id-contact |
Date_start |
Date_end |
Number of contacts |
Days |
|
| 1 |
1 |
846611 |
24-07-2020 |
25-03-2022 |
1,0 |
609 |
1 |
| 2 |
1 |
188672 |
25-03-2022 |
15-11-2022 |
1,0 |
235 |
| 3 |
2 |
1683438 |
09-07-2021 |
25-03-2022 |
1,0 |
259 |
2 |
| 4 |
2 |
82924 |
25-03-2022 |
15-11-2022 |
1,0 |
235 |
| 5 |
3 |
1656267 |
28-06-2021 |
25-03-2022 |
1,0 |
270 |
3 |
| 6 |
3 |
9553 |
25-03-2022 |
16-05-2022 |
1,0 |
52 |
| 7 |
4 |
999736 |
28-09-2020 |
17-11-2020 |
1,0 |
50 |
4 |
