Formula to populate lime item with months for a selected year

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

Accepted answers

DavidSmith

@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

All comments

ChrisAHeathcote

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

DavidSmith

@Sneha

Assuming Time Jan is a formatted time period:

ADDMONTHS(START(Time Jan)+1)

should do it

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 @DavidSmith

Thanks for your response.

Unfortunately some of the other line items in the masterfile module are not dimensionalised by time hence I have to use a SELECT for the time-related line items.

I think the NUX I will be able to do this instead of having, say, synchronised scrolling on a dashboard.

Thanks,

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

ChrisAHeathcote

The non-time dimensioned data should show alongside the 'revenue' line item in the same fashion as any other line item.
The fact that time has been added as a dimension should not have an impact on no-time dimensioned data.

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…