Formula to populate lime item with months for a selected year

[Sneha]

Hello All

I have a Masterfile module that has lines items that use a select function to get the data for a specific month. However, I am now trying to make the module dynamic. This means that the end user can select a year and the module is updated with the months in the selected year. 

I have created a "Masterfile - Time" module were the desired year can be selected but what formula can I use to populate the line items with the months of the selected year? 

Secondly, please find attached an image of the how to the formula is the masterfile uses a LOOKUP function to the Masterfile-Time module.

Please let me know if any further clarification is needed.

Many thanks in advance! 

What formula should i use to get Feb and Mar filled in?The module looks up on the appropriate month in the Masterfile-Time module

[ChrisAHeathcote]

Could you please elaborate on the purpose of the 'Masterfile - Time.'

[DavidSmith · Accepted solution]

@Sneha 

Assuming Time Jan is a formatted time period:

ADDMONTHS(START(Time Jan)+1)

should do it

However, I'm slightly concerned that you have line items representing each period.  The data might be better being held in a module dimensioned by time. That is more of how Anaplan works (i.e. multi dimensional)

David

[ChrisAHeathcote]

@Sneha 

From the second screenshot it appears you are trying to pull through revenue data from using the 'Masterfile - Time' module as a mapping the the time period.

If this is correct then the best route to ensure time is matching against the desired cell is to use the time dimension instead of individual line items. 

Does the, 'production financial input.revenue' module contain a time dimension? I assume it does as you are trying to map it across using a time.month formatted line item. 

If so, create a 'time selection' module containing a single cell ( or as many as is required depending on the number of time periods that need mapping ) format this as a time.month. Use this to map your revenue into the 'master file - production data,' module.

Publish the cell to a dashboard and use the drop down to select the desired month.  

[Sneha]

Hi Chris

Thanks for your response - unfortunately not all the line items in the module are dimensionalised by time.
The masterfile trying to combine two modules, one with time dimension and one without. When the two modules are published to a dashboard - there is no synchronised scrolling and the synchronisation is not effective. So I have created a module that combines the two for end users to be able to analyse and be able to export. I think the NUX is supposed to have a feature that will fix this.
Thanks,
Sneha

[ChrisAHeathcote]

@Sneha it feels like you are trying to squeeze too much into a the master file. 

I have found it more effective to separate time dimensioned data and non time dimensioned data to avoid these very issues.

As long as the modules share a common list then they can combined in a target module/report rather than trying to do this in one large back end module. Use multiple smaller modules containing common list dimensions instead of attempting to create one large catch all master data module.

Can the data be separated and then combined when required in the final output in such a way that you have header details ( non time dimensioned info ) and values ( which are allocated to month.period ).

If so, I recommend trying to break up the data into small bitesized, more manageable modules. 

[Sneha]

Hi Chris,
So the masterfile is supposed to be a "target" module as it combines the time dimensioned and non-time dimensioned data from two separate modules using the a common list. Hence the formulae in the masterfiles retrieve data from "production input.revenue" looking up on a specified month, with production input being a time dimensioned module.
If I have misinterpreted your comment, please let me know.
Thanks,
Sneha

[ChrisAHeathcote]

No you didnt misinterpret my reply. I didnt explain myself well. 

I meant to say that when displaying the result in a dashboard combine the time dimensioned module alongside the non time dimensioned module and synchronise based on the list items. 

I was incorrect in suggesting that they should be combined into a single module but rather displayed alongside each other on a dashboard.  

[ChrisAHeathcote]

Have you considered adding a time dimension to the target module, creating a single line item for revenue ( rather than one for each month ) and using a boolean formatted line item to power a filter on the time period. This would allow your user to select the desire year via a published line item on the dashboard which in turn would activate the boolean line item and inform the filter which periods to show.
If this sounds useful lets work through the detail.

[Sneha]

@ChrisAHeathcote 

This would work for revenue and the time-dimensioned data but what about the non-time dimensioned data? 

The masterfile is a combination of the two and will need to remain this way for business function.

Many thanks,

Sneha