Solved

# Picking a dimension property dependently on scenario

• 55 views

Userlevel 2
+1
• Occasional Observer
• 4 replies

Hi Pigment Experts,

I have the following challenge. I have two properties in a an Employee dimension list (Department_Apr and Department_Mar). Employees may move between departments and these properties are reflecting that.

Then I have a metric by Employee which I want to pivot by Department. I use the formula below and it works well. My question is is there a way to write formula so the metrics is either by Department_Apr or Department_May dependently on a Scenario i.e. dependently on input to another metric? The way it works now is that Department_Apr or Deparment_Mar are hardcoded but how to make these values a variable?

if('Is Forecast (Headcount)', '361. Total Headcount Cost - A+F') [remove: '01. Payroll Waterfall', '01. Headcount Cost Account']
[select: '01. FinType'."P&L"]
[select: '01. Version' = 'Version Push'] //."Mar Month End"]
[filter: '01. Employee ID'.OperRepairs = '01. Operations/Repairs'."N"  and '01. Employee ID'.RetailTR = '01. Retail Branch / TR'."N"]
[by: '01. Employee ID'.Department_Apr]

Regards,

icon

Best answer by Mat 3 May 2024, 19:13

View original

Userlevel 3
+5

Thanks for reaching out on the community with your question!

From what you’re asking, do you have one property per month on your Employee dimension?

If so, I would recommend a different approach:

• For something that is more scalable, you should use metric with a data type of ‘Department‘ Dimension, and structure that metric by Employee and Month.
• You can populate this metric from a transaction list that contains the department changes and propagates the value forwards until the next change for that employee.
• For the purpose of this example, I’ll refer to this metric as ‘Employee_Dept_By_Month
• Once you have this metric, you can replace the employee dimension by mapping with that metric
• The modifier would look something like:
[BY SUM: ‘01. Employee ID’ -> Employee_Dept_By_Month’]
• This would provide you with the result per month, reflecting the employee’s assigned department for any given month, which you can then either filter in the formula, or use a page selector in your view!

You can find out how to use the BY → Mapping parameter in more detail here:

It’s difficult to be more precise as I don’t know the structure of the other metrics involved in your formula, but I hope this gives you a push in the right direction!

I’m happy to elaborate if you’ve got any more questions!

-Mat