Aggregated Calculation over a dynamic date interval
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
