Skip to main content
Solved

Formula not working as expected - TEXTLIST

  • September 25, 2024
  • 5 replies
  • 146 views
Chahat
Master Helper
Forum|alt.badge.img+14

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'.Name[BY TEXTLIST: TIMEDIM('erStart Date',Month)]),
'erCity'.Name[BY TEXTLIST: TIMEDIM('erStart Date',Month)],
IF(ISDEFINED(PREVIOUS(Month)),PREVIOUS(Month),'erCity'.Name[BY TEXTLIST: TIMEDIM('erPlan Date',Month)]))

 

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

City metric in Anaplan with above formula.
 

Best answer by oliverlee

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'.Name[BY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)],
  'erCity'.Name[BY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)], 
  IFDEFINED(
    PREVIOUS(Month),
    PREVIOUS(Month),
    'erCity'.Name[BY TEXTLIST: Role -> TIMEDIM('erPlan Date',Month)]
  )
)

 

5 replies

oliverlee
Master Helper
Forum|alt.badge.img+12
  • oliverleeMaster Helper
  • Master Helper
  • Answer
  • September 26, 2024

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'.Name[BY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)],
  'erCity'.Name[BY TEXTLIST: Role -> TIMEDIM('erStart Date',Month)], 
  IFDEFINED(
    PREVIOUS(Month),
    PREVIOUS(Month),
    'erCity'.Name[BY TEXTLIST: Role -> TIMEDIM('erPlan Date',Month)]
  )
)

 

Chahat
Chahat
Master Helper
Forum|alt.badge.img+14
  • ChahatMaster Helper
  • Author
  • Master Helper
  • September 26, 2024

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

oliverlee
Master Helper
Forum|alt.badge.img+12
  • oliverleeMaster Helper
  • Master Helper
  • September 26, 2024

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

  • erCity
  • erStart Date
  • erPlan Date
Chahat
Master Helper
Forum|alt.badge.img+14
  • ChahatMaster Helper
  • Author
  • Master Helper
  • September 26, 2024
  • 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
Chahat
Master Helper
Forum|alt.badge.img+14
  • ChahatMaster Helper
  • Author
  • Master Helper
  • September 26, 2024

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

oliverlee

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement