LOOKUP formula conditional on time when time is NOT a line item

Highlighted
New Contributor

LOOKUP formula conditional on time when time is NOT a line item

I am working on creating a sales summary for 299 with time along my columns and see that the unit price of products changes over time, as such I would like to create a formula that selects price data dependent on the current time period, e.g., if it's currently January 2017, price of good A should be Z and if it's currently July 2017, price of good A should be W. I've looked into the time formulas to find what I am looking for but have come up empty. 

Message 1 of 4
3 REPLIES 3
Certified Master Anaplanner

Re: LOOKUP formula conditional on time when time is NOT a line item

This works for you: Source Module.Price[SELECT: TIME.'Current Period']. But @DavidSmith pointed out in a different thread that you should prefer dynamic lookups. Then you need an assumptions module with a time period formatted line item (just two examples how you could name the line item).

Source Module.Price[LOOKUP: Assumptions Module.Price Period]

Source Module.Price[LOOKUP: Assumptions Module.Current Period]

 

Then you must assign a value to the assumptions line item with a function. For example the ones above could be: TIME.Current Period or PERIOD(CURRENTPERIODSTART()).

Message 2 of 4
New Contributor

Re: LOOKUP formula conditional on time when time is NOT a line item

Suppose if Price from Jan to May is X , June and July is Y and price from August to December is Z, here we can use the below logic -:
IF ITEM(Time) >= TIME.'Aug 17' THEN 'Z' ELSE IF ITEM(Time) >= TIME.'Jun 17' AND ITEM(Time) < TIME.'Aug 17' THEN 'Y' ELSE 'X'

Here no extra line item is required for time range
Message 3 of 4
Community Boss

Re: LOOKUP formula conditional on time when time is NOT a line item

@Ashishpandit 

Apologies, but that is not a good formula

 

It contains hard coded time selects, breaking Planual rule 1.01-01 and also the ITEM(Time) expression should not be use in any module other than one just dimensioned by time (2.01-07)

 

David

 

 

Message 4 of 4