Occasional Contributor

Formula Assistance

I am trying to build a formula to pull in fringe benefit rates based on the department/unit and employee works for. I attempted to build an IF Then formula but Anaplan keeps telling me it is invalid. See my formula below.

 

IF Dept Code >= 0000 and Dept Code <= 5999 OR Dept Code = 6500 OR >= 7000 and Dept Code <=7199 THEN UPG Admin FB Rates.Fringe Rate[LOOKUP: Staff Types (UPG)] OR IF Dept Code >= 6000 and Dept Code <= 6999 THEN UPG CPG Fringe Rates.Fringe Rates[LOOKUP: Staff Types (UPG)] OR IF Dept Code >= 8700 and Dept Code <= 8799 THEN UPG RPCN Fringe Rates.Fringe Rate[LOOKUP: Staff Types (UPG)] OR IF Dept Code >= 8000 and Dept Code <= 8600 THEN UPG RPCS Fringe Rates.Fringe Rate[LOOKUP: Staff Types(UPG)] OR ELSE 0

 

Any assistance is appreciated.

Thanks,

Davrae

10 REPLIES 10
Frequent Contributor

Re: Formula Assistance

I would strongly suggest you build a system module for departments and use booleans in your calculation. Check out the DISCO methodology.

 

This will make your if statement easier to read and be more efficient.

 

For example, you would create a system module for your department list. Only departments would be in this list (so make it flat - no parents or descendants). Then add line items that help you filter the departments. For instance, if the department code is less than 5999 then "TRUE". 

 

Lastly, it is recommended that if you have a lot of IF, Then, Else, nested together you break them up. 

 

Let me know if this is enough detail. I can create an example for you.

Occasional Contributor

Re: Formula Assistance

Hello Jared,

I am going to have to study this some as I am not following the example. I am not comfortable making any changes or adding to the hierarchy, as our implementation consultants managed the hierarchy and we were not trained on the process. I  will try to study this some more and see what I can come up with. 

Super Contributor

Re: Formula Assistance

Hi,

I second Jared here. You don't need to make any change really.

Just create a module with department as its only dimension and set a fringe rate for every department

Now instead of doing your IF formula, just do 

    Module Name.Fringe Rate[LOOKUP:my department]

where my department is a department list formated line item (that you will probbly obtain with FINDITEM)

Nathan Rudman
Anaplan Model Builder
Frequent Contributor

Re: Formula Assistance

Here's an example which also demonstrates the PLAN methodology and DISCO.

 

First we create a flat list from our structured list that only contains that hierarchy level. In this case we'll focus on the the subclass, our lowest level (this could be your department in your example)

 

Screen Shot 2019-11-07 at 4.04.43 PM.png

 

Following DISCO, or the "S" for System, we create a system module using only the flat list we just created.

This will contain all our booleans for filtering and for nested IF statements.

In the PLAN methodology, this would be the "P" for performance and "A" for auditing.

Notice we create all our booleans here, even the complicated ones. "Calculate Once, Reuse Often"

 

Screen Shot 2019-11-07 at 4.05.05 PM.png

 

Here are the formulas for our system module. Very simple, concise, and easy to understand what they do.

Screen Shot 2019-11-07 at 4.16.16 PM.png

Lastly, we create an Output module, or "O" in DISCO.

Several things to note here:

- The nested if statement is very understandable "A", and uses the booleans from the system module. Very fast ("P")!

- Always set up your IF statements to calculate the most likely scenario first. For example, most of the subclasses for our sales for the C and W less Swim sales (below) will meet this condition, so we test for that first.

 

Screen Shot 2019-11-07 at 4.17.04 PM.png

Hope this helps!

Occasional Contributor

Re: Formula Assistance

Hello Jared,

I read over your response over the weekend and it made more sense to me. My concern is the flat list. Our implementation team built a flat list in the Hub and I am not sure how to move that list from the Hub to Development. Maybe I need to created a flat list in DEV.   Your thoughts? 

Thank you,

Davrae

Occasional Contributor

Re: Formula Assistance

Hi Davrae,
It looks like the logic may need adjustment. Try this.
IF Dept Code >= 0000
AND Dept Code <= 5999
OR Dept Code = 6500
OR >= 7000
AND Dept Code <=7199
THEN UPG Admin FB Rates.Fringe Rate[LOOKUP: Staff Types (UPG)]
ELSE IF Dept Code >= 6000
AND Dept Code <= 6999
THEN UPG CPG Fringe Rates.Fringe Rates[LOOKUP: Staff Types (UPG)]
ELSE IF Dept Code >= 8700
AND Dept Code <= 8799
THEN UPG RPCN Fringe Rates.Fringe Rate[LOOKUP: Staff Types (UPG)]
ELSE IF Dept Code >= 8000
AND Dept Code <= 8600
THEN UPG RPCS Fringe Rates.Fringe Rate[LOOKUP: Staff Types(UPG)]
ELSE 0
~AMW~
Highlighted
Occasional Contributor

Re: Formula Assistance

Hello AMW , 

Thank you for your assistance. Anaplan did not like this formula. 

Certified Master Anaplanner

Re: Formula Assistance

HI @davraes,

I was going thru your formula and found some issues. Please find the corrected formula below. Let me know if that works.

IF (Dept Code >= 0000 and Dept Code <= 5999) OR (Dept Code = 6500) OR (Dept Code>= 7000 and Dept Code <=7199) THEN UPG Admin FB Rates.Fringe Rate[LOOKUP: Staff Types (UPG)] Else IF (Dept Code >= 6000 and Dept Code <= 6999) THEN UPG CPG Fringe Rates.Fringe Rates[LOOKUP: Staff Types (UPG)] Else IF (Dept Code >= 8700 and Dept Code <= 8799) THEN UPG RPCN Fringe Rates.Fringe Rate[LOOKUP: Staff Types (UPG)] Else IF (Dept Code >= 8000 and Dept Code <= 8600) THEN UPG RPCS Fringe Rates.Fringe Rate[LOOKUP: Staff Types(UPG)] ELSE 0

 

Regards,

Kavin.

Super Contributor

Re: Formula Assistance

@davraes 

 

First its invalid because there is no logic in the formula and you need to add few more paranthesis here and there as @kavinkumar suggested.

 

However @Jared Dolich has already given you much more simpler, faster and effective approach of doing it.

 

Happy Planning!

Misbah