Aggregate all employees salaries and reference total in another calculation
Dear All, actually I have a scenario that I need to perform.
Please check the below scenario :
• I have a custom dimension named ‘Employee’ containing employee IDs and the ‘Entity’ dimension representing the employees departments.
• Some data elements are forecasted on the employee level and the rest of the elements are forecasted on the entity level.
• Such as, “Salary” is forecasted on employee level and specific entity in form 1, while ‘Bonus’ is forecasted on cost center level in form 2. So we need to use total salaries entered on all employees in form 1 in the calculation forecasting the bonus in form 2.
• Example:
o Employee 1, entity 1 has salary 1000
o Employee 2, entity 1 has salary 2000
o I need to calculate the bonus based on the 3000, which is the total of all employees in entity 1.
How can I do this ?
I tried the following:
• I tried adding each employee’s salary to a dummy employee named ‘no employee’, so that I can select this dummy account to contain all employees salaries total entered earlier when forecasting on entity level. But the problem is that it loads only 1 value, each time we press save in the form, the last value overwrites. So it contains 1 value not the total.
o Salary->no employee=salary;
• I tried creating a parent level for employees named ‘all employees’ and classified it as ‘DynamicCalc’, and tried to write after forecasting on employee the following to write it on the dummy level:
o salary->no_employee=salary->’All_Employees’. But it didn’t work as well.
• I tried also to refer to entity directly:
o Salary->no employee=salary->RTP_entity; it didn’t work
• I tried using : salary->no_employee=salary->@Idescendants(‘All employees’), but it didn’t work as well
Can you please advise what can I do ? the above options are just trials, I’m not sticking to them. I only want to perform the scenario I mentioned at the beginning with any possible way.
Thank you very much in advance.