Dear everybody,
I have an algorithm question
I need to build a table that display several data and one date but I need to keep these data only if it fits the following condition:
Based on an interval of 10 days, I need to display :
- the first line
- the first line whom date exceed previous date by at least 10 days
- the next first line whom date exceed previous date by at least 10 days
- and so on
All calculations needs to be dynamic as
- interval will vary using a variable on user side
- start date will depend on selection
- at the end, I do not need to do this in a table ... i just have to "count" all "Yes" line using an aggregate function
Please find an example below
| ID | Date | Gap | | | Keep line ? | Why |
| A1 | 01/01/2020 | | | | Yes | First line |
| A2 | 08/01/2020 | 7 | | | No | 7 days from A1 |
| A3 | 23/01/2020 | 15 | | | Yes | 22 days > 10 from A1 |
| A4 | 28/01/2020 | 5 | | | No | 5 days from A3 |
| A5 | 11/02/2020 | 14 | | | Yes | 19 days > 10 from A3 |
| A6 | 18/02/2020 | 7 | | | No | 7 days from A5 |
| A7 | 19/02/2020 | 1 | | | No | 8 days from A5 |
| A8 | 20/02/2020 | 1 | | | No | 9 days from A5 |
| A9 | 31/03/2020 | 40 | | | Yes | 49 days > 10 from A5 |
| A10 | 08/04/2020 | 8 | | | No | 8 days from A9 |
| A11 | 26/04/2020 | 18 | | | Yes | 26 days > 10 from A9 |
| A12 | 12/05/2020 | 16 | | | Yes | 16 days > 10 from A11 |
| A13 | 23/05/2020 | 11 | | | Yes | 11 days > 10 from A13 |
I'm trying to achieve this calcaulation using above(), rangesum() ... without success until now
Thanks in advance for any help you could provide
