If Formula Error Message

jdeluca

I need help with a formula.  In a module to calculate 401K match for employees, I have the below.  There are 2 types of TM's - "Regular" and "PRN".  The below works perfectly for "Regular" TM's.  However, I want to add an IF statement to the TM Total Salary calculation that basically says IF TM Type = 'Regular' THEN use formula currently there ELSE use a different line in the SWB Cost module for the salary reference (Allocated Salary instead of Total Salary) .  However, I get the error message that 'Regular' is not a recognized line item or list member.  Any advice?

 

401K Match Module...

Info from SWB Cost module...

LipChean_Soh

Hi,

 

Can you try using double quote i.e. "TEXT" instead of 'TEXT' ?

 

But besides the above, i would like to point out a few things:

1. TM Type is a property of L5 TM List. Then TM Type shouldn't be a text format, because Text format captures everything, and there's a possibility you might add/ load a TM Type in the future that is misspelled, therefore giving you the wrong calculation somewhere within the model. You should have a TM Type as a list formatted property, i.e. built TM Type as a list before using it as a property of L5 TM List.

 

2. I don't know the business logic behind 'TM Total Salary', but hardcoding it to Time.FY19 seems a bit permanent. Now maybe that could be the case, but it's still better to use a LOOKUP instead of SELECT, and refer it to a 1 cell module on the Admin dashboard, where the entry is FY19.

 

3. The 0.03 and 3600 are hardcoded values that makes it hard to be tracked and modified in the future if needed. Making 1 change is easy if there's proper documentation and the model builder is familiar with the entire setup, but imagine if such hardcoded values are throughout the entire model. If any number is missed, the wrong calculation happens. Also note that for hardcoded values, you'll need to make the change in the DEV model before running Compare and Sync (I'm assuming you have ALM). Using LOOKUP to refer to Assumption modules on the Admin dashboard should solve the issues mentioned above.

 

Thanks,

LipChean

jdeluca

Thanks for the suggestions, LipChean, but still no luck. 

 

Can you try using double quote i.e. "TEXT" instead of 'TEXT' ? 

I have tried both single quote & double-quote to no avail

 

But besides the above, i would like to point out a few things:

1. TM Type is a property of L5 TM List. Then TM Type shouldn't be a text format, because Text format captures everything, and there's a possibility you might add/ load a TM Type in the future that is misspelled, therefore giving you the wrong calculation somewhere within the model. You should have a TM Type as a list formatted property, i.e. built TM Type as a list before using it as a property of L5 TM List.

At your suggestion, I added TM Type in a list and included only Regular & PRN in the list.  I then added as a property in the L5 TM List formatted as List, not Text.  Using this as a reference in my 401K formula still gives error message as in my original post.

 

2. I don't know the business logic behind 'TM Total Salary', but hardcoding it to Time.FY19 seems a bit permanent. Now maybe that could be the case, but it's still better to use a LOOKUP instead of SELECT, and refer it to a 1 cell module on the Admin dashboard, where the entry is FY19.

In this scenario, I am looking to use the total for FY 19 and SELECT works fine for what I am doing.

 

3. The 0.03 and 3600 are hardcoded values that makes it hard to be tracked and modified in the future if needed. Making 1 change is easy if there's proper documentation and the model builder is familiar with the entire setup, but imagine if such hardcoded values are throughout the entire model. If any number is missed, the wrong calculation happens. Also note that for hardcoded values, you'll need to make the change in the DEV model before running Compare and Sync (I'm assuming you have ALM). Using LOOKUP to refer to Assumption modules on the Admin dashboard should solve the issues mentioned above

I may add the .03 & 3600 values to a current assumption module.  Thanks for that suggestion.

 

LipChean_Soh

Hi,

 

In that case, can you share screen shots of the 401k and SWB Cost blue prints?

 

Do create the line item in the 401k module even if it's a blank field at the moment due to the faulty formula, and type out in full the formula that gave you the error in the community.

 

Thanks,

LipChean

ArunManickam

if item('L5 TM List') = 'L5 TM List'.'Regular' then <<Regular calculation>> else <<PRN calculation>>

 

To select the list member, use the mouse and select the list member, instead of typing the formula, that will prevent the error you are facing.

 

You have a text field for the list L5 TM List, try to avoid it.

 

Thanks

Arun

jdeluca

Thanks for the additional help.  I will try & respond further later today.  Other priorities this morning.

jdeluca

Thanks for the reply. I will try & respond further later today. Other priorities this morning.

jdeluca

I have figured it out.  I updated my 401K Calc module to bring in the allocated salary and added an "AND" to my IF statement.  Thanks for your assistance.

 

IF '401K Match Eligible' AND 'L5 TM List'.TM Type = "Regular" THEN TM Total Salary * 0.03 ELSE IF '401K Match Eligible' AND 'L5 TM List'.TM Type = "PRN" THEN TM Allocated Salary * 0.03 ELSE 0

 

jdeluca

I have figured it out.  I updated my 401K Calc module to bring in the allocated salary and added an "AND" to my IF statement.  Thanks for your assistance.

 

IF '401K Match Eligible' AND 'L5 TM List'.TM Type = "Regular" THEN TM Total Salary * 0.03 ELSE IF '401K Match Eligible' AND 'L5 TM List'.TM Type = "PRN" THEN TM Allocated Salary * 0.03 ELSE 0

 

rob_marshall

@jdeluca ,

 

Glad you figured it out...One point that should be noted, you have the .03 hardcoded.  It would be better if you created a "threshold" or "percentage" module (not dimensionalized by any list) and use a look vs embedding the .03 within the module.  This way, if it ever changes, you change it in one place vs. having to search all of the line items in the model.

 

Thanks,

 

Rob

jdeluca

Thanks, Rob.  I think that had also been suggested by previous repliers.  I was so focused on getting the "IF" part worked out for Regular vs PRN, I did not think too much on that.  I'll add that to my next steps.