Skip to main content

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') Aremove: '01. Payroll Waterfall', '01. Headcount Cost Account']
sby: '01. Employee ID'.Department_Apr]

 

Regards,


Adam

Hi Adam!

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:
      rBY 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

Reply


Ask your question here!

Terms & ConditionsCookie settings