Subtracting Months when using lookup

Hello.. I'm trying to figure out how to write a lookup formula that will subtract months.

For example, I have the following line items

Hire Date (Month format) Set for Jun 19

Revenue (Number format) Has 100 for Jan and each month increases by 100 (so 200 for Feb, 300 for Mar, etc)

Test Result (Number Format)

In Test Result, I'm trying to create a formula like: Revenue[Lookup: Hire Date] - 1 (Thus subtract 1 month from Hire date, so instead of June, I would get May's revenue number), but the different formula combinations I have tried, doesn't seem to work

Revenue[Lookup Hire Date - 1] (didn't like the formula)

Revenue[Lookup 'Hire Date' -1] (didn't like the formula)

Revenue[Lookup Hire Date] - 1. (liked the formula, but got back 599, which wasn't correct)

Thanks!!

Accepted answers

All comments

Misbah

Hi,

If there is a time dimension it can be done in multiple ways

Try Using the formula:

Revenue - OFFSET(Revenue,-1,0)

Revenue - PREVIOUS(Revenue)

Thanks,

Misbah

C0000

When I tried either of these formula's, the result wasn't the expected answer

Misbah

Can you please share a screenshot What exactly are you trying to achieve. I might have misunderstood your requirement.

C0000

Under Test Formula, I'm trying to write a formula like:

Test Formula: Revenue[Lookup Hire Date] - 1 (- 1 meaning subtract one month), which would populate May's number since May one month prior to the Hire Date of June

Misbah

@C0000

You may have to use IF ELSE function here. Can you use the below formula and see if this works

IF ITEM(Time) >= Hire Date THEN Revenue - PREVIOUS(Revenue) ELSE 0

Hope this helps!

Misbah

jnoone

Hi, you can create the following line items:

1. "hire date" - manual entry (can be linked to non-time dimensionalized module)

2. "month lookup" - formula is "period(hire date) - 1" with format of "month".

3. "revenue" - manual entry

4. "test result" - formula is "Revenue[LOOKUP: Month Lookup]"

This will give you May value in the month of June. Screenshot also attached. I think this should accomplish what you need or get you close but let me know if not the case. Also looks like revenue is the only line item which is changing over time so you can change the applies to, to not applicable for the rest.

-Jt

hire_date_example_49346.png

Pooja

Hello,

For this scenario, create separate line time to get Hire Date-1as shown below then use it in Lookup clause.

Time(Hire date-1).JPG

C0000

Hi Pooja... Your mentioning of adding additional line items and subtracting # from hire date and doing the lookup with the new line item works (jnoone also mentioned the same thing). Except the module I'm working with is very large and was trying to see if I could cut down the number of cells by adding what the "Hire Date - 1" line item were doing within the formula on the "Test" line item in your example.

A few minutes ago, I created another module like in the picture below. My thinking was seeing if could Match Hire Date to Mnth and bring back the specified "Month - #" that I wanted to use, but not sure if that would work either

VIGNESH.M

if you are concerned about the size, you can remove the time dimension of Hire date , Hire date-1 line items.

These 2 line items will take only 2 cells without time dimension. Its simple and you are getting the answer.

~Vignesh M

Pooja

Hi,

I just tried Timesum just to avoid extra line item to be created, it worked as expected(attached screenshot for same). time sum.JPG

DavidSmith

Better still to have those subsidiary views in a separate module

rob_marshall

@Pooja ,

Please don't use TimeSum with a module using the time scale as this may cause performance issues. TimeSum is really meant to be used on a module without time scale.

Thanks,

Rob

Misbah

@Pooja

Why would you use TIMESUM on a module where you have time dimension. TIMESUM should be used on a line item without Time Dimension 😛

By the way I liked the way you have done it. Definitely not as per Anaplan Practices though.

C0000

The Timesum formula works, as I'm getting the expected result.

Can I ask why using timesum is discouraged if the module is dimensioned by time?

I can change the time scale from "Month" to "Not Applicable". My question is if I have a bunch of employees that all have a different start month, (thus Hire Date - 1, Hire Date - 2) would all have different results, if switching the time dimension would cause an issue (Besides creating a subsidary view of those line items)

rob_marshall

@C0000 ,

Sure, because TimeSum was designed to be used when the TimeScale was not define. It is better and more efficient to use previous, Offset, or a lookup from a SYS Time module. Take a look at Anapedia for TimeSum and you will see the following:

More information can be found here:

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/TIMESUM.html?_ga=2.80058775.1638387830.1563799135-1916962816.1554898270

Thanks,

Rob

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…