I want to each row to calculate the number of columns, which match the following criteria:
(1) the created date has to be <= the created date of the specific line (2) the close date has to be <= the created date of the specific line
Example: For ID = 4, there are 2 rows, which match the criteria (ID 1 + ID 2). ID 3 does NOT match the criteria, since the close date is after the created date of ID 4. Therefore, the counter should not count this lins.
ID
Created
Closed
Counter
1
01.01.2020
02.01.2020
0
2
02.01.2020
02.01.2020
1
3
03.01.2020
05.01.2020
2
4
04.01.2020
04.01.2020
2
5
05.01.2020
05.01.2020
4
I know that I can word with rangesum(peek(, but I do not get this working, taking into account that I have the filter criteria which I need to get into this formula.
What I started with:
RangeSum(Above(TOTAL Sum(if(CreatedDate<=CreatedDate and CloseDate<=CreatedDate, Counter)), 0, RowNo(TOTAL)))
I'm not sure if this is possible within the UI. If it would probably need something like:
if(Date1 >= above(Date1) and Date2 <= above(Date2), ...
and this wrapped with at least one aggr() with the right dimensionality and ordering - whereby I assume that the shown ID is more a record-counter as an ID from a business point of view ... In each case it would be complex.
More practicable may be to transfer the essential logic into the script by using interrecord-functions like peek() and previous() within n loads to create n flags (forwards and backwards and ordered against Date1 and Date2 and maybe some more information). Developing the logic in small steps - and if some working is found it may optimized again.
Beside this I suggest to re-think the entire approach because there may more suitable ways to get the wanted views - maybe starting with a resolution of the period-areas to dedicated dates per internal while-loop or an intervalmatch.
thank you very much for your efforts & help - as always really appreciated! Scary to hear that this is not possible. In EXCEL, theoretically, this is nothing of an issue. Unfortunately, the system simply can't handle the number of rows.
I also tried simple left joins, so joining each row to each row, which also blows up the capacity - since we are talking about roughly 1.2 billion rows in my dataset. 😄
If I summarize your thoughts - and honestly, not sure if I understand everything - there is no direct way to do it. Strange. 😞 I would have thought, that it would be possible to check previous values against the value in the actual row. This is not possible? o.O
A check against the next row is simple but against an unknown number of rows could become difficult. In general each row could be accessed but which one / ones should it be from the calling point? It requires a logic which would catch each scenario - the common ones as well as the seldom exceptions.
Therefore my suggestions to create n flags / offset-results to simplify the logic. Even if I overcomplicated the matter and only the previous record needs to be evaluated a 0/1 flag-field which is accumulated in the next step might be quite useful in regard of simplification and UI performance (which won't be good by such data-set and queries with interrecord-functions within (nested) if-loops and/or aggr() constructs).
counting doesn't work since I do not want to aggregate the data - since I just want to countif (in EXCEL terms).
@marcus_sommer The possible workaround you mentioned exceeds my knowledge 😄 And counting the commulative values above (which I already tried), does also not solve the issue, since it can be that there is a value behind which would also be relevant.
I have now solved it using good old EXCEL VBA. Takes longer to run through the data, but at least it works.
