Using Sum with Filter on dates Not working

(0) Share

Report

Posted on by SearchingYT

107

Hi Power apps experts,

My datasource is sql server with more than 5000 records.
I have an int column "Number", a date column "CreatedOn" and a text column "Name" in my sql_table.

When I perform Sum using filter with "Number" and "CreatedOn" - it works:

Sum( Filter(sql_table, Name="ABC"), Number) - it works as expected with delegation on more than 5000 records.

But, when I perform Sum using filter on date column "CreatedOn" - it doesn't work - it returns nothing, blank on my label:

Sum( Filter(sql_table, CreatedOn = DateValue("1900-01-01")), Number)

Does anybody know why.

Are you facing the same issue at your end?

Any help would be appreciated. Thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
According to the Delegation documentation on SQL Filtering on a date column doesn't work. Check the table here:

SQL Server - Connectors | Microsoft Docs

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like (0)

Report

Hi @Pstork1

Okay. If Delegation doesn't work for SQL, then why does this it work in this formula: (on same dataset)
Filter(sql_table, CreatedOn = DateValue("1900-01-01"))
E.g. this correctly shows all the filtered items in the gallery, without any delegation warning or issues.

What am I missing here?

UPDATE:
For me, delegation works on Date type with SQL. Its only in Sum function it doesn't work for me:
Sum( Filter(sql_table, CreatedOn = DateValue("1900-01-01")), Number) - returns the wrong Sum.
But, If I collect it in collection first, e.g:
ClearCollect( tmp, Filter(sql_table, CreatedOn = DateValue("1900-01-01")))

And then in a label I do:
Sum( tmp, Number)

This works for me. Can anybody explain why?

Issue: If my filter part contains more than 2000 records, then this wouldn't work because of collection size limitation...

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

There may have been a recent update that makes dates delegable for filtering. That's not what the documentation says and it hasn't been my experience in the past. But even if it does work Sum() is not a delegable operation. So if the Filter() doesn't get your data set below the data row limit then Sum() will only work on the first 500 - 2,000 records depending on your data row limit setting. If you can get your filter below the limit then the fact that Sum() isn't delegable won't matter. You'll still get the delegation warning, but you can ignore it if you know the filter is below that limit.

It all works in a local collection, because collections aren't subject to delegation limits since they are local. But there are performance and freshness of data concerns when working with large local collections.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like (0)

Report

@Pstork1
Interestingly, to check delegation of Sum() on 5000+ records, when I do:
Sum( Filter(sql_table, Type = "TypeA"), Number)
This works and returns the correct sum value.
Thus, in sql datasource, sum() seems to be delegable on int datatype (i.e. my Number column)

Exactly, collections makes the app slow as well. I really want to avoid that.

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

If you do a Countrows() on Filter(sql_table, Type="TypeA") how many rows do you get? If its more than 2,000 Sum won't give you the full result. If its less than 2,000 it will. I'm assuming its less than 2,000

Was this reply helpful? Yes No
SearchingYT 107 on at

Like (0)

Report

@Pstork1
I just checked on my end:
CountRows( Filter(sql_table, Type="TypeA")) returns 2000
and
Sum( Filter(sql_table, Type="TypeA"), Number) returns 3764

So, I believe Sum() is now delegable on SQL datasource (on column type int)

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

I just looked again, and the documentation has been updated. But SUM only works on a number column

SQL Server - Connectors | Microsoft Docs

Was this reply helpful? Yes No
SearchingYT 107 on at

Like (0)

Report

@Pstork1 That makes sense.

But, going back to the original issue, do you know why this works:
Sum( Filter(sql_table, Name="ABC"), Number)

and this does not work:
Sum( Filter(sql_table, CreatedOn = DateValue("1900-01-01")), Number)

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

I'll fall back on my original answer. Filtering on a Date field isn't supported as delegable according to the documentation. If that Filter isn't delegable then it doesn't matter that Sum() is because the filter is done first and the Sum is based on the result of the filter.

Was this reply helpful? Yes No