Platform

ChrisHeathcote

Using QUARTERTODATE is simply an automated way of populating the mapping and would maintain the mapping as time frames are updated. Otherwise the mapping would need to be manually updated each time the model calendar was changed. Perhaps my contribution is more of an explanation as to why this would be a good approach!!

ChrisHeathcote

I am not sure using QUARTERTODATE as described by others gives you the full explanation. If you are looking to have a query which directs the outcome to a specific action such as the 2nd period of each quarter results in a certain outcome etc. then you need to create a custom mapping against time.quarters which will direct the query to the desired action. If this is so, create a numbered list corresponding to each desired unique action. Then map this list to each quarter in a time settings systems module. In your target create your IF THEN statement using this systems module to map to the desired action. Example - IF targetcell.value[LOOKUP: mapping.unique action] then x else next query...etc * by Action I refer to any desired outcome which has been mapped in the time setting systems module. Hope this helps. Chris

ChrisHeathcote

First impression here is that you formula is far to long. Break out your formula across more line items. Secondly, you are using hard coded numbers which really should be coming from an input module or a list. From the nature of what you appear to be trying to do the Dept Code should be a list. This should either be a property of the employee list or a list formatted line item in a system module for employee details. Create an input module containing the department code list as a dimension and benefit rate as a line item. In your target module which should be dimensioned by the employee list use LOOKUP to pull the rate through per employee dependent on the department. The LOOKUP should reference the department property of the employee list or list formatted line item of the employee details module. There are a few steps required but in summary; 1 Create a new list of department codes. 2 Create a property of the employee list or list formatted line item in an employees details system module. 3 Create a new module containing the departments list containing one line item contain the fringe benefit rate 4 Use LOOKUP in your target module to take the rate relevant to the employees department querying the employee list property or list formnatted line item Where you are trying to build a formula with more than two queries this should raise alarm bells and prompt you to seek out a simpler solution. Hope this helps. Chris

ChrisHeathcote

The trick here is that you need to establish a relationship between lists via a composite hierarchy and then build a series of line items dimensioned at different levels which feed into the summary of the next level in the hierarchy Build a filter system module dimensioned at the lowest level of the hierarchy. Add line items representing each level of the hierarchy to be present in the filter. Format as number. Create a filter selection module with line items for each level of the hierarchy. Format as boolean. Back to the filter module for each of these line items create subsidiary views for each corresponding level in the hierarchy other than that at the lowest level. For each line item create a formula which queries the selection and returns 1 when true and 0 when false. Create two additional line items in the filter module called technical and final filter ( this last one will feed the filter on the dashboard ). Technical type =1 in the formula bar and select formula as the summary method. Final filter = the line item corresponding to the lowest level. For each line item summary method use ratio. Set each summary to query the line item at the next level up in the hierarchy as the numerator and the technical line item as the denominator. See how each line item is at a different level in the L hierarchy

ChrisHeathcote · ‎08-17-2019

Hi All, I am using Line item subsets to create a series of P&L statements at varying levels of detail. Is it possible to synchronise these line items across modules when publishing to a dashboard? In the dashboard it is possible to assign parent items to line items which follow a simple sum but they are restricted to items within the same module. Is it possible to synchronise line items across two or more independent modules. If so, how can this be achieved. Thanks, Chris

ChrisHeathcote · ‎07-26-2019

@usha.cherukuri I would recommend creating a new list called something like, 'variance message,' and populate it with as many members as you have potential messages calling each a number, such as 1, 2, 3 etc. Using this new list create a new module containing one text formatted line item. For each number in the variance message list write the desired message. We will use this module to map the text across into the target module. In your target module containing the variance calculations create a line item which queries the variance result and returns the desired number relative such as 1 for 'no difference,' 2 for 'corrective action required.' Call this line item variance message code. These should correspond with the line item contents in your variance message module created earlier. In the variance message line item in the target use the following formula =variance message module.variance message[SELECT:variance message list.variance message code]

ChrisHeathcote · ‎07-26-2019

Have you tried using a numbered list? If so, and the error persists it would appear that you are trying to create a parallel hierarchy. Create a duplicate second hierarchy using the desired parents and children ( this parallel hierarchy will contain a duplicate parent and the second child ) Create a mapping module which maps the two hierarchies using the parallel hierarchy's child list as the dimension and the primary hierarchy's parent list as a list formatted line item. Establish the relationship between parent and child and use this mapping when creating aggregation using the parallel hierarchy.

ChrisHeathcote · ‎07-25-2019

@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

ChrisHeathcote · ‎07-25-2019

@jkinnick What you are faced with here is a mapping problem - how can Anaplan map the 'Lease ending date,' to the 'Bi-weekly ending dates.' Mappings can be established either within list properties or a module. However, as the mapping is from a one to many we can not use list properties as we will not be able to add a range of dates to each 'Bi-weekly ending date,' in a list property. Therefore, I would recommend building a new module dimensioned by the Anaplan 'Time' dimension and containing one line item which has been formatted as the list, 'Bi-weekly ending date,' and name this line item as per the list name, 'Bi-weekly ending date.' Then for each date in the time dimension allocate it a, 'Bi-weekly ending date,' list item. I would recommend that you build this in excel and import via a csv as this may be time consuming. Now you are ready to build the formula which will reference the mapping module and pull the relevant Bi-weekly ending date. As long as your target module contains a time dimension all you need to do is reference the 'Bi-weekly ending date,' in the source ( the mapping module ) to pull through the relevant bi-weekly ending date. Even if you add additional dimensions to your target this should no impact on how the b-weekly ending date pulls through to your target. Just ensure that the line item in your target is formatted to DATE. Hope this helps, Chris

ChrisHeathcote · ‎07-25-2019

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

ChrisHeathcote · ‎07-25-2019

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

ChrisHeathcote · ‎07-25-2019

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

ChrisHeathcote · ‎07-25-2019

@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

ChrisHeathcote · ‎07-25-2019

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

ChrisHeathcote · ‎07-25-2019

@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

ChrisHeathcote · ‎07-24-2019

@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

ChrisHeathcote · ‎07-24-2019

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

ChrisHeathcote · ‎07-24-2019

The SUM and LOOKUP formulas are used to map dimensions between modules where a direct link does not exist. Therefore, in order to successfully reproduce the formula in other modules it must contain enough mapping definitions in order to establish the required links. Example - if you are using SUM to aggregate hours by BUs and employees in a target module where the source contains just the BU dimension you must be map in the target module formula how each employee relates to each BU. Say you have an employee dimension in the target in addition to BUs which is not present in the source you must specify how employees are linked to each BU. This can be achieved by using a mapping module with the employee dimension and a LIST FORMATTED LINE ITEM of the BUs. Select a BU for each Employee and use SUM to build the link between the target and source modules. The SUM would look something like this; = Module one.Hours[SUM: Mapping.Employees] Module one is the source contain details of the hours by BU Mapping is the mapping module telling the target which BUs in the source relate to which employees in the target. SUM is used when you have many aggregating into one - as above LOOKUP is used when aggregating one into many - which employee belongs to which BU I hope this helps to clear the mist that may have formed from all the other comments. Chris

ChrisHeathcote · ‎07-22-2019

@ArunManickam Yes, you are correct. ITEM will return a time period format NOT a date format which would not be compatible with DAY(). In that case I would also recommend your solution - DAY(START())

ChrisHeathcote · ‎07-22-2019

@aabalos Hi, Could this not be solved by amending the current hierarchy? This would create a direct relationship across districts and sub-districts. If this is not possible then an alternative method of establishing the relationship/mapping is required. You will have to create a new list at the high level and create a mapping module with the lower level as the dimension and the higher level as a list formatted line item ( assign the new list to this line item ). Select the new list member as the correct mapping and use this module to aggregate your values when creating your reporting modules. I would recommend trying to adapt your hierarchy instead of the manual mapping approach. An updated hierarchy will ensure that your aggregations roll up logically and will not require you to create aggregation formulas every time you need to report at the higher level.

ChrisHeathcote · ‎07-22-2019

An alternative approach is to use a combination of ITEM and DAY. =Day(ITEM(Time)) This formula will query the current time period and then return the day value formatted as a NUMBER.

ChrisHeathcote · ‎07-19-2019

@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

ChrisHeathcote · ‎07-19-2019

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

ChrisHeathcote · ‎07-19-2019

Did you manage to resolve the issue? If so, what technique or method did you use?

ChrisHeathcote · ‎07-19-2019

@Takamasa SWITCHOVER is linked to the versions within the model and enables the model builder to allocate actual results to that version. Example - A full year has 12 periods. The current period is Period 3 and the user would like to create a new forecast to reflect changes in the business and the competitive environment. In business change is constant and organisations must make plans to reflect this. When creating this new forecast the model builder can turn on SWITCHOVER and select period 4 as the switchover period. As the model builder creates the new forecast in this new version values upto period 3 will pull from the ACTUAL version while values from period 4 will SWITCHOVER to the new forecast version. It is used to ensure that the new forecast includes actual data for those periods that have passed as a forecast can no longer be created for these periods as actuals are known. Hope this clarifies. Chris Heathcote

ChrisHeathcote · ‎07-19-2019

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.

ChrisHeathcote · ‎07-18-2019

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.

ChrisHeathcote · ‎07-17-2019

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.

ChrisHeathcote · ‎07-17-2019

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.

ChrisHeathcote · ‎07-16-2019

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.

Platform

Academy

Master Anaplanner

Programs

About ChrisHeathcote