Distinct group by and counts

Like (0) Share

Report

Posted on 18 Aug 2023 10:27:42 by venka91

1,118

Dear Team, For a SPList, i am trying to group the employees by Department and then get the count of them. Sounds easy. But i have been trying to derive the formula, which is not giving me the expected result.

Employee1	650	Information Technology	11	4	1	3
Employee2	651	Information Technology	11	3	2	3
Employee2	651	Information Technology	11	3	2	2
Employee1	650	Information Technology	11	4	1	3
Employee1	650	Information Technology	11	4	1	3
Employee2	651	Information Technology	11	3	2	3
Employee1	650	Information Technology	11	4	1	3
Employee2	651	Information Technology	11	3	2	1
Employee1	650	Information Technology	11	4	1	3
Employee2	651	Information Technology	11	3	2	1
Employee2	651	Information Technology	11	3	2	2
Employee1	650	Information Technology	11	4	1	3
Employee1	650	Information Technology	11	4	1	2
Employee2	651	Information Technology	11	3	2	2
Employee2	651	Information Technology	11	3	2	3
Employee1	650	Information Technology	11	4	1	2
Employee2	651	Information Technology	11	3	2	3
Employee1	650	Information Technology	11	4	1	2
Employee2	651	Information Technology	11	3	2	2
Employee1	650	Information Technology	11	4	1	2
Employee1	650	Information Technology	11	4	1	1

AddColumns(Distinct(Filter(SPList,HodFlag ="Completed"),EmpDept),"CountDept",CountRows(Distinct(Filter(SPList,HodFlag ="Completed"),EmpDept)

my gallery should show me something like this. But i am getting count as 1 instead of 2.

There are two employees in Information Technology. So gallery rows will be displaying Department Names, and the Employee count on it.

You can ignore the HOD flag column as i have just made this column to make sure this record is verified by head of the department.

Later tried the formula.

AddColumns(GroupBy(AWPR_Responses,"EmpDept","DeptGroup"),"EmpCount",CountRows(Distinct(ThisRecord.DeptGroup,EmpFullName)))

Giving me the result in the gallery as

Please help to validate it ?

Categories:

Using Apps Using Formulas

All responses (4)

Answers (0)

developerAJ 2,855 on 19 Aug 2023 at 04:30:57

Like (0)

Report

Re: Distinct group by and counts

thisrecord.deptgroup and saying deptgroup

thisrecord.deptgroup is something you need to use in forall it respresent single item

deptgroup is table.
venka91 1,118 on 18 Aug 2023 at 11:35:23

Like (0)

Report

Re: Distinct group by and counts

Just for my understanding, is there any change between thisrecord.deptgroup and saying deptgroup alone ?
developerAJ 2,855 on 18 Aug 2023 at 11:21:54

Like (0)

Report
Re: Distinct group by and counts

AddColumns(GroupBy(AWPR_Responses,"EmpDept","DeptGroup"),"EmpCount",CountRows(Distinct(DeptGroup,EmpFullName)))

this should do just change thisitem.deptgroup to deptgroup
gcmfaizan 1,022 on 18 Aug 2023 at 10:40:47

Like (0)

Report
Re: Distinct group by and counts

Hi @venka91 ,

for example here is my employee data:
ClearCollect( colEmployees, Table( { EmployeeName: "John", Department: "HR", Salary: 50000 }, { EmployeeName: "Jane", Department: "IT", Salary: 60000 }, { EmployeeName: "Michael", Department: "Finance", Salary: 55000 }, { EmployeeName: "Emily", Department: "IT", Salary: 62000 }, { EmployeeName: "David", Department: "HR", Salary: 48000 }, { EmployeeName: "Sara", Department: "Finance", Salary: 58000 }, { EmployeeName: "Daniel", Department: "IT", Salary: 61000 } ) )

and here is the formula to count the employee as per Department:
ClearCollect( colDepartmentCounts, AddColumns( GroupBy(colEmployees, "Department", "Group"), "Temp_Department", Group, "Temp_Count", CountRows(Group) ) )

here is the output:

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.