Subtracting Months when using lookup

Options
C0000
C0000 Member, ALL USERS Posts: 5 New Contributor
in Planning & Modeling

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

Tagged:

Comments

  • C0000
    C0000 Member, ALL USERS Posts: 5 New Contributor

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

  • C0000
    C0000 Member, ALL USERS Posts: 5 New Contributor

    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

  • C0000
    C0000 Member, ALL USERS Posts: 5 New Contributor

    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

  • C0000
    C0000 Member, ALL USERS Posts: 5 New Contributor

    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)

Categories