Highlighted
New Contributor

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!!

15 REPLIES 15
Highlighted
Community Boss

Re: Subtracting Months when using lookup

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

Highlighted
New Contributor

Re: Subtracting Months when using lookup

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

Highlighted
Community Boss

Re: Subtracting Months when using lookup

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

Highlighted
New Contributor

Re: Subtracting Months when using lookup

Anaplan_0725_Example.JPG

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

Highlighted
Community Boss

Re: Subtracting Months when using lookup

@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

Highlighted
Master Anaplanner/Community Boss

Re: Subtracting Months when using lookup

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

 

 

Highlighted
Master Anaplanner/Community Boss

Re: Subtracting Months when using lookup

Better still to have those subsidiary views in a separate module
Highlighted
Contributor

Re: Subtracting Months when using lookup

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

Highlighted
New Contributor

Re: Subtracting Months when using lookup

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

 

Anaplan_0725_Example3.JPG