Hello @Para ,

This is an interesting question and depending upon the information in your transactional list can be tackled in different ways. I am going to imagine that your transactional list contains Employee, Department and Salary, as in the following example:

In this case, you can see what is the salary for each employee, so you could aggregate the salaries by Employee and Month - as I guess you currently have- by using the following formula:

'Employee Salary'.'Yearly Salary'aby firstnonblank: 'Employee Salary'.Employee]yadd:Month]/12

Please note that I am adding Month as a dimension because there is no mapping available in the example for Month, and I am dividing by 12 given that we have a Yearly salary. As you had a monthly salary, you can probably avoid the 12.

The above would result in something like the image below:

Nevertheless, you are interested in the aggregation by by Department and Month, which can be achieved in a similar way: 

'Employee Salary'.'Yearly Salary' by: 'Employee Salary'.Department]aadd:Month]/12

I hope the above makes sense, please do let me know if I can clarify any point.

Cheers!

@BrunoRodriguez Thanks for your kind help. Actually my list did not contain the salary per se, but the hourly rate per employee so aggregation of hourly rate made no sense at this point. Salaries were calculated in a separate metric. However, I succedeed aggregating salaries at dept level by using a mapping metric,  using this formula:

'Staff Data'.DepartmentnBY FIRSTNONBLANK: 'Staff Data'.Name]

which I guess is correct.

Cheers!

Hello @Para ,

Fantastic news, great job!

I guess that with your formula you are trying to map the Department to each specific Employee and then using the result to aggregate the Employee/Month metric into Department/Month. Would that be correct?

The only additional caveat I would consider is checking whether you are storing every month of loaded data in the transactional list (i.e, every month the data is uploaded again into the transactional list and all the data is stored in Pigment). If that is the case, you may have to look at the latest information in case an employee changes their department.

Cheers!

@BrunoRodriguez Yes, that is perfectly correct. For now, I am just playing with some mockup data, only to “make my hand” so it’s about a single month for now, no further granularity :)

Thanks again for your kind support, this is much appreciated

Hi,

Additionnally, I would recommend you to enroll to an Academy Course where you could learn all about Pigment and multidimensionnality.

This one for example is a great training path to get a modeler certification:

https://academy.gopigment.com/path/modeling-fundamentals-certification

Hope this helps.

Best,