Skip to main content
Solved

Picking a dimension property dependently on scenario

  • April 26, 2024
  • 1 reply
  • 134 views
A
Master Trendsetter
Forum|alt.badge.img+1
  • AdamMaster Trendsetter
  • Master Trendsetter

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

Best answer by Mat

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

Rebe
CDALMAY

1 reply

Mat
Wallflower
Forum|alt.badge.img+6
  • MatWallflower
  • Wallflower
  • Answer
  • May 3, 2024

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

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement