Solved

Picking a dimension property dependently on scenario

  • 26 April 2024
  • 1 reply
  • 43 views
A
Userlevel 2
Badge +1
  • Adam
  • 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,


Adam

icon

Best answer by Mat 3 May 2024, 19:13

View original

1 reply

Mat
Rank
Userlevel 3
Badge +5

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

Mat | Pigment Specialist

Reply


Ask your question here!

Terms & ConditionsCookie settings