Skip to main content

In Pigment, I have tried the below formula for City metric: 
The source metrics are with dimension: Employees and Role
The target metric is having dimension: Employees and Month.
I’ve tried the above formula, but it’s giving me the same abc value across for employees and for all months.
Can anyone assist me with this formula in Pigment 

IF(ISDEFINED('erCity'.NametBY TEXTLIST: TIMEDIM('erStart Date',Month)]),
'erCity'.NametBY TEXTLIST: TIMEDIM('erStart Date',Month)],
IF(ISDEFINED(PREVIOUS(Month)),PREVIOUS(Month),'erCity'.NametBY TEXTLIST: TIMEDIM('erPlan Date',Month)]))

 

IF(ISNOTBLANK('Employees by Role'.CityoTEXTLIST: 'Employees by Role'.Start Date])
THEN 'Employees by Role'.CityoTEXTLIST: 'Employees by Role'.Start Date]
ELSE
	IF
		ISNOTBLANK(PREVIOUS(City))
	THEN
		PREVIOUS(City)
	ELSE
		'Employees by Role'.CityoTEXTLIST: 'Employees by Role'.Plan Date]

City metric in Anaplan with above formula.
 

Hi! I think what’s missing is the arrow within the modifier to make sure that your employee dimension is not removed through the BY modifier.

IFDEFINED(
  'erCity'.NamemBY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)],
  'erCity'.NamemBY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)], 
  IFDEFINED(
    PREVIOUS(Month),
    PREVIOUS(Month),
    'erCity'.NamemBY TEXTLIST: Role -> TIMEDIM('erPlan Date',Month)]
  )
)

 

I have added the arrow now,
Getting error : IFDEFINED argument 2 and 3 need to have the same type
@oliverlee 

@pigment_enthusiast would you mind sharing the exact dimensionality and type for all the metrics you’re referencing?

  • erCity
  • erStart Date
  • erPlan Date
  • erCity: Employees and Role
  • erStart Date: Employees and Role
  • erPlan Date: Employees and Role
    Target Metric is with dimensions: Employees and Month 
    Data Type: City

Hey @oliverlee  Thanks!
It’s working now, changed the data type of Target Metric from City dimension to Text

Reply


Ask your question here!

Terms & ConditionsCookie settings