topic Re: Sum by Department in App Development

Sum by Department

rlanger — Fri, 27 May 2022 20:14:24 GMT

I have the following data and I need to display the sum of the ammount paid by department in order to calculate the % every employee represents.

My problem is that I don't want to show the Department.

Department	Employee	Amount Paid
Enginnering	Pedro	23.214,15
Enginnering	João Miguel	8.431,04
Enginnering	Francisco	6.352,07
Enginnering	Rodrigo	17.992,42
Sales	Alexander	0,00
Sales	Murilo	1.004,89
Sales	Joaquim	11.017,41
Sales	Fernando	0,00
Sales	Marcelo	1.197,05
Sales	César	998,66
Sales	João	16.550,91
Sales	Gustavo	7.380,69
Sales	David	0,00
Sales	Eduardo	2.893,43

The best I could get is this, but I can't get the sum on every line.

Employee	Amount Paid	Aggr(sum(Amount_Paid),Department)
Pedro	23.214,15	-
João Miguel	8.431,04	55.989,68
Francisco	6.352,07	-
Rodrigo	17.992,42	-
Alexander	0,00	-
Murilo	1.004,89	-
Joaquim	11.017,41	-
Fernando	0,00	-
Marcelo	1.197,05	-
César	998,66	-
João	16.550,91	-
Gustavo	7.380,69	-
David	0,00	41.043,04
Eduardo	2.893,43	-

Re: Sum by Department

Vegar — Fri, 27 May 2022 22:43:06 GMT

Try adding a NODISTINCT to your aggr(), like this:

Aggr( NODISTINCT sum(Amount_Paid),Department)

Re: Sum by Department

stevedark — Fri, 27 May 2022 22:46:00 GMT

Hi @rlanger

If you did have the department in the table then it would be a simple expression:

sum(TOTAL <Department> [Amount Paid])

However, I can't find a simple way of hiding the extra column. You could use an autonumber in the load, and then have a column which just has values of 1, 2, 3 etc. for each department, so:

LOAD
Department,
AutoNumber(Department) as DeptNo,

Then use DeptNo as the dimension and replace Department with DeptNo in the expression above.

Another approach is to calculate the Department totals in the load script and then have that in a separate table, like this:

Data: LOAD * INLINE [ Department,Employee,Amount Paid Enginnering,Pedro,23214.15 Enginnering,João Miguel,8431.04 Enginnering,Francisco,6352.07 Enginnering,Rodrigo,17992.42 Sales,Alexander,0.00 Sales,Murilo,1004.89 Sales,Joaquim,11017.41 Sales,Fernando,0.00 Sales,Marcelo,1197.05 Sales,César,998.66 Sales,João,16550.91 Sales,Gustavo,7380.69 Sales,David,0.00 Sales,Eduardo,2893.43 ]; DepTotal: LOAD Department, sum([Amount Paid]) as [Department Paid] RESIDENT Data GROUP BY Department;

You can then simply do sum([Department Paid]) to get the department totals.

The big advantage of this approach is that if you make a selection on an indivdual then you will still be able to see their percentage correctly:

Hopefully that gives you what you need to get to the result that you are trying to achieve.

I've attached the app that I used to test.

Good luck!

Steve

Re: Sum by Department

rlanger — Thu, 02 Jun 2022 14:13:44 GMT

Thanks Vegar! It worked!

One important thing I learned is that your data needs to be perfect.

For example: I had the same Employee with 2 Departments, including null sometimes.

With this problems the formula would bring Null in this cases.

Re: Sum by Department

rlanger — Thu, 02 Jun 2022 14:10:01 GMT

Thanks stevedarjk!

It worked too!