Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
No idea ?