Conditional Count if for entire column

[florian_kloster]

Hi all,

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)))

Can anybody help me?

Thanks
Florian

[marcus_sommer]

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.

[florian_kloster]

Hi Marcus,

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

Thank you very much,
Florian  

[mattewwade06]

This is a great use case for a conditional aggregation. You might try using something like: Count({<ColumnName={'value'}>} ColumnName)just adjust the field and condition based on what you need. Also, make sure your data model supports the condition properly. Hope that helps!

Ask C

[marcus_sommer]

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). 

[florian_kloster]

Hi Marcus,
Hi Mattew,

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. 

Thank you for your help!!