Subtracting Months when using lookup

C0000

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

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

 

 

Pooja

Hello,

 

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

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

Hi 

 

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).

 

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