Answered

Help with summarizing list data for unique names within the current pay period.

(1) Share

Report

Posted on by P3DERSEN

I have a sharepoint list that i need to display a summary of in powerapps. This list contains itemized entries of every employees hours for a given day and project.

I have 2 date pickers that give the start date and the end date of the current pay period. StartDate & EndDate.

What i need to do is show a sum of the number values for each Team Member, for the date ranges between StartDate and EndDate.

Essentially what I want to make possible, is for the user who is inputting a teams time entries to be able to quickly view a summary of their employees, and see their total hours for the current pay period, to ensure no one is missing hours.

The column names and types are:

'Team Member' - Single Line of Text
'Date Worked' - Date
'Project Worked' - Single Line of Text
'Regular Hours Worked' - Number
'Overtime Hours Worked' - Number
'Double Time Hours Worked' - Number
'Sick Hours' - Number
'STAT Hours' - Number
'Vacation Hours' - Number

The formula I have for the blank gallery (which is not working), is:

AddColumns( GroupBy( Filter( 'Time Sheets', 'Date Worked' >= StartDate.SelectedDate && 'Date Worked' <= EndDate.SelectedDate ), 'Team Member', "GroupedItems" ), "TotalRegularHours", Sum(GroupedItems, 'Regular Hours Worked'), "TotalOvertimeHours", Sum(GroupedItems, 'Overtime Hours Worked'), "TotalDoubleTimeHours", Sum(GroupedItems, 'Double Time Hours Worked'), "TotalSickHours", Sum(GroupedItems, 'Sick Hours'), "TotalSTATHours", Sum(GroupedItems, 'STAT Hours'), "TotalVacationHours", Sum(GroupedItems, 'Vacation Hours') )

Any help on what I’m doing wrong? Is there a better way to acheive this?

Categories:

General Questions

All responses (6)

Answers (1)

WarrenBelz 141,157 on at

Like (0)

Report

Help with summarizing list data for unique names within the current pay period.

Hi P3DERSEN,

The fundamental issue is that GroupBy() is a "local" function and the amount of records able to be actioned by it is restricted to your Data Row Limit. You can run a delegable filter inside it (as you have) to retrieve the desired data set (so you can get 1,900 records from the filtered result on a data source of 10,000), but once the filtered data gets over 2,000, you will not receive the complete results. The only real workaround if this situation arises is to always use one or both of the Team Member or Project filters as well as the date range to bring the set number down appropriately.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
P3DERSEN 10 on at

Like (1)

Report

Help with summarizing list data for unique names within the current pay period.

WarrenBelz, this is solved.

Your initial comment did in fact work. I had the deligation limit set to 500 items. I changed this to 2000, and it corrected it.

My next question would be... what will i need to do once this limit is reached again? Is there a better way to do this?
P3DERSEN 10 on at

Like (1)

Report

Help with summarizing list data for unique names within the current pay period.

Hey WarrenBelz,

Yea that formula results in the same output... I can try to explain the issue better.

Without any filters on, and with the date range set ie. Sep.15 (StartDate) & Sep.28 (EndDate)

The sum of any users hours seem to be missing almost half of the actual list entries for that user between those 2 dates.

HOWEVER, if I turn on a filter (either for member, or project), the sum is correct. The sum should be the same regardless if any filters are on, as these filters are only to refine who you're looking for.

I have also tried manually inputting the dates into the formula with no luck, so it isn't my date pickers causing the issue.

Does this make more sense?

Appreciate the help,
WarrenBelz 141,157 on at

Like (1)

Report
Help with summarizing list data for unique names within the current pay period.

Hi P3DERSEN

I cannot see your data so that is a bit hard to answer other than you have the correct syntax to sum each of those fields in each of those grouped 'Team Member' sets. The date filters will affect the totals results, but the others will only affect the number of grouped records. This is a small revision, but I do not think it will affect the output (I am not exactly sure what you mean with the 40 and 80 hours as only the date filters should dictate these.
SortByColumns( AddColumns( GroupBy( Filter( 'Time Sheets', 'Date Worked' >= StartDate.SelectedDate && 'Date Worked' <= EndDate.SelectedDate && ( Len(ComboBox8.Selected.'Employee Name') = 0 || 'Team Member' = ComboBox8.Selected.'Employee Name' ) && ( Len(ComboBox9.Selected.'Project Name') = 0 || 'Project Worked' = ComboBox9.Selected.'Project Name' ) ), 'Team Member', GroupedItems ), TotalRegularHours, Sum( GroupedItems, 'Regular Hours Worked' ), TotalOvertimeHours, Sum( GroupedItems, 'Overtime Hours Worked' ), TotalDoubleTimeHours, Sum( GroupedItems, 'Double Time Hours Worked' ), TotalSickHours, Sum( GroupedItems, 'Sick Hours' ), TotalSTATHours, Sum( GroupedItems, 'STAT Hours' ), TotalVacationHours, Sum( GroupedItems, 'Vacation Hours' ), ProjectWorkedDisplay, If( CountRows( Distinct( GroupedItems, 'Project Worked' ) ) = 1, First( Distinct( GroupedItems, 'Project Worked' ) ).Value, "Varies" ) ), "ProjectWorkedDisplay", SortOrder.Ascending )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
P3DERSEN 10 on at

Like (1)

Report

Help with summarizing list data for unique names within the current pay period.

WarrenBelz, That got me somewhere near where i need to be, however my version is not summing correctly.. ie a team member will have 80 hours on 1 project, the overall sum will show 40 hours, whereas if i filter the gallery now by the employee or the project, it then shows the full 80 hours... Why would this be happening?

Here is the formula i have finalized using your suggestion, I added a filter & an if statement if they worked on multiple projects:

SortByColumns(
AddColumns(
GroupBy(
Filter(
'Time Sheets',
'Date Worked' >= StartDate.SelectedDate &&
'Date Worked' <= EndDate.SelectedDate &&
(IsBlank(ComboBox8.Selected.'Employee Name') || 'Team Member' = ComboBox8.Selected.'Employee Name') &&
(IsBlank(ComboBox9.Selected.'Project Name') || 'Project Worked' = ComboBox9.Selected.'Project Name')
),
'Team Member',
GroupedItems
),
TotalRegularHours,
Sum(
GroupedItems,
'Regular Hours Worked'
),
TotalOvertimeHours,
Sum(
GroupedItems,
'Overtime Hours Worked'
),
TotalDoubleTimeHours,
Sum(
GroupedItems,
'Double Time Hours Worked'
),
TotalSickHours,
Sum(
GroupedItems,
'Sick Hours'
),
TotalSTATHours,
Sum(
GroupedItems,
'STAT Hours'
),
TotalVacationHours,
Sum(
GroupedItems,
'Vacation Hours'
),
ProjectWorkedDisplay,
If(
CountRows(
Distinct(GroupedItems, 'Project Worked')
) = 1,
First(Distinct(GroupedItems, 'Project Worked')).Value,
"Varies"
)
),

"ProjectWorkedDisplay",SortOrder.Ascending)
Verified answer

WarrenBelz 141,157 on at

Like (1)

Report
Help with summarizing list data for unique names within the current pay period.

Hi P3DERSEN,

You do not need quotes "" around either the AddColumns or GroupBy parameters.
AddColumns( GroupBy( Filter( 'Time Sheets', 'Date Worked' >= StartDate.SelectedDate && 'Date Worked' <= EndDate.SelectedDate ), 'Team Member', GroupedItems ), TotalRegularHours, Sum( GroupedItems, 'Regular Hours Worked' ), TotalOvertimeHours, Sum( GroupedItems, 'Overtime Hours Worked' ), TotalDoubleTimeHours, Sum( GroupedItems, 'Double Time Hours Worked' ), TotalSickHours, Sum( GroupedItems, 'Sick Hours' ), TotalSTATHours, Sum( GroupedItems, 'STAT Hours' ), TotalVacationHours, Sum( GroupedItems, 'Vacation Hours' ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee