Sum of Hours from SharePoint List within Date range

[AlexDefault]

Hi,

So I've been trying to figure out how to do a simple sum of all hours.

I saw that this should work:

Sum(Filter('Job Time Tracker',Title = "Job Name"),Hours)

However the formula states, Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "Sum" operation is not supported by this connector.

The hours column is formatted as a number and is calculated based on two other date columns. This is a SharePoint list data source.

Additionally it would be useful to be able to get the sum of hours within a certain date range.

Any help would be great! 

[mdevaney]

@AlexDefault 

Firstly, the SUM function cannot be delegated in SharePoint.  If your datasource exceeds 2,000 rows finding the SUM will not be possible.

To get the SUM of of hours you would need to have the values stored as a decimal number rather than time.  Do you already have this in your datasource?

Example: 02:30 = 2.5 hrs.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[AlexDefault]

Actually this might be the problem,

Just checked the column settings and it's set to number, but the calculated data is using TEXT()

Is there a better option for working out the hours based on start and end time?

[mdevaney · Accepted solution]

@AlexDefault 
Actually, calculated columns are all read-in to PowerApps as Text.  I know you specified it to be a number, but PowerApps sees it as Text.  That's just a fact of life...

Why not just insert the value for End-Start when your form is submitted in PowerApps?  It would remove any need for a calculated column.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[AlexDefault]

I see,

I suppose that could work, so calculate the hours worked in the Power App and submit that calculation into a static column. Then I could in theory sum up that column instead. 

[mdevaney]

@AlexDefault 

Yes, that's how I do it.  But keep in mind PowerApps will not allow you to SUM the column past 2,000 rows.  You might have to do something tricky here like send to Flow and wait for a response.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[AlexDefault]

Okay I'll keep that in mind. For now I've got very few records. We'll see if I ever get to 2,000

[mdevaney]

@AlexDefault 

Please try to make the suggested change.  If it works, make sure to Accept as Solution for my post above.

[AlexDefault]

Just some useful snippets for calculating the difference between two times. I use two separate calculations for hours and minutes. These are then Patched to two columns in the SharePoint. 

DateDiff(
    StartDate.SelectedDate + Time(
        Value(StartDateHour.Selected.Value),
        Value(StartDateMin.Selected.Value),
        0
    ),
    EndDate.SelectedDate + Time(
        Value(EndDateHour.Selected.Value),
        Value(EndDateMin.Selected.Value),
        0
    ),
    Hours
) 

Mod(
    DateDiff(
        StartDate.SelectedDate + Time(
            Value(StartDateHour.Selected.Value),
            Value(StartDateMin.Selected.Value),
            0
        ),
        EndDate.SelectedDate + Time(
            Value(EndDateHour.Selected.Value),
            Value(EndDateMin.Selected.Value),
            0
        ),
        Minutes
    ),
    60
)

For the sum its simply this:

Sum(Filter('Job Time Tracker',Title = "Job Number"),'Hours_Worked') + (Sum(Filter('Job Time Tracker',Title = "Job Number"),'Minutes_Worked')/60)

[RezaDorrani]

Hi @mdevaney , @AlexDefault ,

I came across a useful API in SharePoint to get aggregate function values like Sum. etc. 

We can avoid delegation by getting a little help from flow.

Check it out:

https://youtu.be/qsIn0mytC_8

Thanks,
Reza Dorrani, MVP
YouTube
Twitter