About ChrisHeathcote

@MarkT83    You are correct. Apologises. It will return an error as it is attempting to return a list item not the line item.   For my solution to work you will have to use SELECT to direct Anaplan to take the top level summary item of the list in question.    It should work by using the following =SourceModule.SourceLineItem[SELECT:Machines.Total Machines]   May I ask why you are reluctant to use SELECT?   Chris ... View more

  When entering the target module to retrieve the figures simply select the top level as shown above.   Chris ... View more

Hi Mark, As far as I can see from your screenshots the dimensionality of the target and the source are the same. You only need to use aggregation formulas like LOOKUP if you need to bridge dimensions where there are differences in the dimensionality of the target and source modules. As this is not the case I did not include them in my solution. Chris ... View more

Hi Mark, There is not function as all you a doing is directly referencing the top summary of the line item. ... View more

@MarkT83    I will assume that you are attempting to retrieve the 'Total Machines,' value of 9,553,403 for each machine type. If so, the solution is quite straightforward. Simply select the, 'Total Machines,' summary in the source to pull that through to the target   =Historical Sales.Total Machines   Chris  ... View more

Hi David, Yes, I agree. As soon as you find you are embedding more than 2 formulas alarm bells should be ringing. Chris ... View more

@jkinnick   @nathan_rudman  A much simpler solution if you are able to successfully match the options against the import and one which should always be sought before any other more convoluted alternative. Chris ... View more

@jkinnick  There are a number of text formulas that can be combined to enable the extract of the day, month and year to allow you to use PERIOD(DATE()) to build up a time period formatted line item.    I will assume that you would like to use these entries to map a time dimension. Therefore, we will use the above formula to create the final time period formatted line item.    Taking your example; May 5 2019 12:00AM   First you must ensure that you load the date into a TEXT formatted line item as these formulas only work for text strings NOT numbers or dates.    Firstly, I would recommend amending your month list to only include the first 3 letters of each month ( Jan, Feb, Mar, Apr etc ) this is important later in the solution when we write the formula to retrieve the month number.   Also, add a property to the list and name it, 'month length.' For each month input the the number of characters in the full month name ( January = 7, February = 8, March = 5 etc ) this will provide a reference to allow us to extract the day number from the text string.    Retrieving the Month number.  We will use a combination of CODE(), FINDITEM() and LEFT().   Create a new line item called, 'Month Number,' formatted as NUMBER. Assuming you have amended you month list use the following formula. =CODE(FINDITEM(MonthsList.LEFT(DateLineItem,3))) This formula is extracting the first three characters of the date line item, retrieving the relevant list item from the Months list and returning the corresponding code.    Retrieving the Day number We will use a combination of MID(), LEN() and the month length property to extract the day number.   Create a new line item called, 'Day Number.' Formatted as NUMBER. Populate this line item with the following =LEN(MID(DateLineItem,MonthsList.month length+1,2))   Retrieving the Year number We will use a combination of LEN(), FIND() and MID()   Create a new line item called, 'Year Number,' formatted as NUMBER   Using the day number line item we can combine the above formulas to extract the year number.   =LEN(MID(DateLineItem,FIND(DateLineItem,Day Number)+1,4))   The three formulas above should enable you to retrieve the the YEAR, MONTH and DAY numbers as from the text formatted date. From here you can either convert them into a DATE or PERIOD formatted line item. If you are unsure which one you will need later in your modelling I recommend converting into both.   Create two line items called, 'Date' and 'Period,' respectively. Set the format of 'Date' to date and 'Period' to whichever time period you require ( most likely months ).   To convert to date use =DATE(Year Number, Month Number, Day Number) To convert to period use =PERIOD('Date')    You may need to tweak these slightly to fit your naming convensions but the principles outlined here should enable you to convert you date into a date or time period line item.    However, I would recommend performing a second import from this module into one which contains a time dimension. Further modelling will be more efficient and effective when utilising the inbuilt Anaplan time dimension rather than relying on line items to define time/dates.   Good luck, Chris ... View more

@VarshniR  You are welcome.  Please reach out via these forums if you have further queries.  Chris ... View more

@PavanKumar  That was my first thought but the very first opening balance needs to be established which requires someway of determining where the time period range starts.   OFFSET(1,-1,0)=0 determines the existence of a time period immediately prior to the opening balance. if the time period is the the first in the range then a zero will be returned and the formula will resolve to a TRUE boolean result if it is not then the formula will resolve to FALSE.   An alternative to OFFSET is PREVIOUS('1')=0  '1' is an additional line item  ... View more

@GaryPage  I did not consider setting up the 1st opening balance in my answer.  @DavidSmiths solution uses a combination of OFFSET and PREVIOUS.    OFFSET(1,-1,0) = 0 has a very specific purpose here. It is determining whether that line item has any periods preceding it - ( the 1 refers to period 1 which is the start of the offset and the -1 the period immediately prior to period 1 which is the destination. It is attempting to retrieve the value of the cell 1 period in the past from the current period. If this is not possible a zero will be returned. As this is impossible for the 1st period then zero is the answer which as the whole formula equals zero will return a true result - a tick in the box ).   This then sets up the conditions possible to populate the first opening balance with PREVIOUS being used to pull these balance forward to the next period.  ... View more

In this example PREVIOUS would be the preferred formula over OFFSET as it is a simple progression from one period to the next. OFFSET would be preferred if you were looking at retrieving a value more than 1 period away. I would suspect that PREVIOUS would also be a more efficient formula for the outcomes you are looking for. ... View more

The solutions suggested appear unnecessarily complicated when it would be straightforward to add a parent to the COA list for all items relevant to Gross Profit. The hierarchy would produce the required roll up and aggregation. Another alternative is to create a consolidated P&L module using a line item subset as suggested by others and allow the user to drill down to the COA list where required. ... View more

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. ... View more

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. ... View more

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.     ... View more

@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.  ... View more

@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.   ... View more

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