topic Re: Conditional Count if for entire column in App Development

Conditional Count if for entire column

florian_kloster — Tue, 08 Jul 2025 14:06:16 GMT

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

Re: Conditional Count if for entire column

marcus_sommer — Tue, 08 Jul 2025 14:27:37 GMT

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.

Re: Conditional Count if for entire column

florian_kloster — Tue, 08 Jul 2025 14:35:13 GMT

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

Re: Conditional Count if for entire column

mattewwade06 — Tue, 08 Jul 2025 15:01:13 GMT

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!

Re: Conditional Count if for entire column

marcus_sommer — Tue, 08 Jul 2025 15:26:03 GMT

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

Re: Conditional Count if for entire column

florian_kloster — Thu, 10 Jul 2025 06:21:39 GMT

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