Count total occurrences of a value per day

[marumaru]

Hi everyone,

I've been working on this requirement for hours now but cannot seem to get the final results that I want. I need to display the total count of a manpower type per day in a gallery, example below.

Date	Steelman	Project Manager
07/02/2023	2 of 3	1 of 1
07/03/2023	1 of 3	1 of 1

I have 2 SharePoint lists as my data source:

1. Masterlist - holds all the time in per day of an employee, please see sample data below.

Masterlis

2. Project details - holds the overall details per project where I will be getting the total count to be expected per day, please see sample data below.

Project Details

So what I did was to collect all the time in which is already filtered by project code and date range (specific week) and only show the Time IN date and Manpower Type below:

ClearCollect(
    colTimeINRecords,
        ShowColumns(colRecordByProjCode,"DateIN","ManpowerType")
);

This is how the collection looks like:

Then, I group the data by Date

ClearCollect(
    colRecordsByDate,
        GroupBy(colTimeINRecords,"DateIN","ByDate")
);

Collection looks like this:

Table in ByDate looks like this:

Now, I want to count the Manpower Type per date and display them on my gallery and can't seem to produce results. Wondering if this is actually possible? 

[LaurensM · Accepted solution]

Hi @marumaru,

You can use the AddColumns & CountRows function to count the amount of grouped records. Additionally, I will remove some of the collections by using the With() function:

With(
    {wRecords: ShowColumns(colRecordByProjCode,"DateIN","ManpowerType")},
    AddColumns(
        GroupBy(
            wRecords,
            "DateIN",
            "ManpowerType",
            "groupedRecords"
        ),
        "jobCount",
        CountRows(grouped)
    )
)

You could use this With() function as a parameter of a ClearCollect() to save it to a collection or display it in a gallery. In a gallery, reference ThisItem.jobCount to get the number of people of a certain profession that were present on that day. The records are grouped per day and job profession (fictive example):

DateIN	ManpowerType	groupedRecords	jobCount
Today	Steelman	Table	2
Today	Warehouseman	Table	3
Yesterday	Steelman	Table	1

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[marumaru]

@LaurensM Thank you for this solution, I was able to get the count per day but wondering if how will I show it in my gallery as the table below:

Date	Steelman	Warehouseman	Project Manager
07/02/2023	2 of 5	0 of 3	1 of 1
07/03/2023	1 of 5	1 of 3	1 of 1

I get the expected headcount which is in bold in the above table from the Project Details list which shows this data:

Do I merge them?

[LaurensM]

@marumaru,

Do I understand it correctly that you would like to group the collection by date and display each profession count as a column in that row? (instead of having a row for each profession & day combination)

The following code should do the trick:

With(
    {
        wRecords: ShowColumns(
            colRecordByProjCode,
            "DateIN",
            "ManpowerType"
        )
    },
    AddColumns(
        GroupBy(
            wRecords,
            "DateIN",
            "groupedRecords"
        ),
        "jobCountSteelman",
        CountIf(
            groupedRecords,
            ManpowerType = "Steelman"
        ),
        "jobCountProjManager",
        CountIf(
            groupedRecords,
            ManpowerType = "Project Manager"
        ),
        "jobCountWarehouse",
        CountIf(
            groupedRecords,
            ManpowerType = "Warehouseman"
        )
    )
)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[marumaru]

@LaurensM hhmmm, I think I just need to add a column for the expected head count of that row item's ManpowerType but that expected head count will come from a different collection/list which is the project details list. Then, I think I would now be able to display it on my gallery.

[LaurensM]

You could leverage a LookUp within the Gallery itself for that, you don't need to save it within the collection. The following code can be written in the Text property of a label within the gallery:

LookUp(
    'Project Details',
    'Project Code' = ThisItem.ProjectCodeColumn && Position = ThisItem.ManpowerType && DateColumn = ThisItem.DateIN,
    'Manpower Count'
)

I am not fully familiar with how your current collection that is displayed in the Gallery looks like. In order to filter you might need to add a reference to the project code. Should the Position name only be available in the Grouped table you might need to hardcode that part (e.g. Position = "Steelman").

I hope this helps!

[marumaru]

Thank you for the help! I already marked your first response since it solved my very first issue and then I was able to find ways with your next responses on my follow up questions. Really great help, thank you!