About ChrisHeathcote

ChrisHeathcote

My first thought was to create a manual mapping as you suggest but this would require ongoing maintenance which I try and avoid. This approach will create two part weeks ( week 1 and week 53 ). If the user wishes to create mapping with complete weeks then we need to fix the weekday number to equal that of the first day of the year. We could achieve this by adding an extra line item in the Time Properties - Year module where we state what WeekDay number the first day of each year is. If we amend the WeekDay and NewWeek formula above to reference this number rather than using a hard coded reference then the WeekNumber count will start from the first day of the year and increase by one every 7 days. As you cant embed YEARVALUE into another function create a new line item in the Time Properties - Day module and use the following formula to reference the firs day of each new week. YEARVALUE('Time Properties - Year'.DayNumber of new week)

ChrisHeathcote

Hi @akprasa1 Given you model calendar restraints I would recommend using the following approach; Create a 'Time Properties - Year' module containing only time dimension set to Year. Add a single line item called, 'Start Date.' Type the following formula - START() This will populate the line item with the date of the first day of each calendar year. Create a 'Time Properties - Day' module containing only time dimension but this time set to Day. Add the following line items; 1. Date, format date, formula START() 1. WeekDay, format number, formula WEEKDAY(Date, 1) The '1' indicates the start of the week, Mon =1, Tue=2 etc 2. NewWeek, format number, formula IF WeekDay = 1 OR NewYear THEN 1 ELSE 0 3. NewYear, format boolean, formula Date = YEARVALUE('Time Properties - Year'.Start Date) 4. WeekNumber, format number, formula CUMULATE(New Week, NewYear) Set all summary options to none. The resulting table can be used to LOOKUP the week number for any given date within the model calendar depending on which day of the week each week starts. Adjust the WEEKDAY function to change this. I have attached some screenshots to help you see what I have suggested. Good luck, Chris

ChrisHeathcote

Hi @Sravan_Kumar On reflection the mapping in my first reply would not be sufficient to give you the outcome I described. To be able to query any source data using the additional country-product list you will will need to create a mapping module dimensioned by your country and master product list containing a list formatted line item for you country-product list. You can populate this mapping manually for every valid combination or use FINDITEM where you are able to combine elements of each list; Country and Master Product into the code of your new list. I would recommend combining the first three letter of each list item as use this as your code for the new number country-product list. You can then use LEFT function to extract the relevant parts of each list name to use in the FINDITEM function. Once you have created this list use it to populate a country-product line item in your source data which can then be used as a mapping in any SUM function which queries it. Please let me know if you need me go into further detail. While this approach may require some upfront investment in modelling it will be the most effective way to achieve your desired outcome. Good luck, Chris

ChrisHeathcote

Hi, I agree 'Selective Access,' is the best approach to providing access rights to specific lists. However, I would add an additional point. While maintaining a master product list I would also add a child branch to your countries to indicate only those products available in each. Use a numbered list as you will be repeating the name of some products. Devise a code from the first two letters of each country and the first two of the product. Once these are created you can use, 'Selective Access,' as indicated but only restrict access to each country as the access restrictions apply to all child branches. This approach will also allow you to keep your model cell count to a minimum helping to improve model efficiency and performance. This is because we will only be creating cells that will be used and therefore keeping sparsity to a minimum. If we were to use the master list for all countries then there will be many combinations of country and product which will be empty and redundant. You will need to create a mapping from your Country-Product list to the Master Product list indicating for each combination of Country-Product which product in Master Product list it relates to. When referencing source data which contains the Master Product list use SUM to map across the two dimensions. In the source module add a line item and format it as the Country-Product list. Directly reference the Country-Product to Master Product mapping module to pull in the relevant Country-Product list item for each master product. You will need to use a LOOKUP function such as; Country-Product Mapping Module.Master Product Mapping[LOOKUP:Source data module.Master Product] The final line item in the function is a Master Product List formatted line item in the source data module. Use FINDITEM to create this if you do not have this already. Once you have created the mapping in the source you are then able to map this to a target module using the following SUM function. Source module.source line item[SUM:Source module.Country-Product Mapping] This approach allows you to keep redundant cells to a minimum and to manage access rights via a single list. If you need further clarity let me know and I will provide a more detailed explanation. Good luck, Chris

ChrisHeathcote

That we should all simplify every so often. This time has forced as to adapt and for many to seek out simple solutions to our day to day challenges. I am enjoying the time, space and stillness that this crazy time has afforded us!

ChrisHeathcote

Hi, I do not believe it is possible to achieve the outcome you require using your current approach. This is because Anaplan does not have the functionality to format a line item with a mathematical operator such as -, +, x or / One way that this could be possible is to create a list to hold you operators ( -, +, x or / ). Format the line items above with this list. However, you will need to create an option for each permutations ( 5^2 = 25 ) The result formula will then need to cycle through each in order to find the correct combination. The formula would consist of a series of nested IF..THEN..ELSE functions each one tested a single combination. It would be possible to reduce the size of the IF..THEN..ELSE statement you could hold the calculations in a systems module and pick the relevant line item based in the combination of the two options. However, this will create a number of empty cells or sparsity as it is most likely you will only ever be using a fraction of the options available. Heavily nested IF statements will lower model performance if they are too long. Therefore, I would question why you need this functionality! I would recommend that you plan and define your Measures and KPIs individually and from the outset. I realise this is not the answer you sought but what you are trying to do is not possible.

ChrisHeathcote

@GuruAP and @anirudh If you intend to use the MEAN calculation across multiple modules then I would create a new module and remove the time dimension. TIMESUM formula can be used to calculation the mean AVERAGE. The formulas in this new module refer to line items which should be created in a current time properties module. If you have already created such a module to hold all your current time properties add the following two line items. If you have yet to build this module do so now as it will prove invaluable as the model develops. This new time properties module should contain no dimensions and only the following line items. Module name - Current Time Properties 1. Line item name - Current Period, Format - Time period months, formula - PERIOD(CURRENTPERIODEND()) 2. Line item name - Current Year, Format - Time period years, formula - Current Period 3. Line item name - Current Year Start, Format - Time period months, formula - PERIOD(START(Current Year)) In new module create the following three line items; Module name - MEAN Calculation 1. Name - Mean, Format - Number, Formula - TIMESUM(source module.source line item, Current Time Properties.Current Period, Current Time Properties.Current Year Start, AVERAGE) Notice how I have used the current period and current year start line items from the time properties module to inform the function over which periods to calculate the MEAN. As the current period is updated so will the range over which the calculation operates. Therefore, it updates dynamically as the year progresses. Now rather than using subsidiary view to hold the MEAN calculation you can now reference it throughout you model if the circumstances required are the same. This ensures that you are calculating once and referencing many times a key point in managing the size and performance of your models. This approach also does not require you to use any LOOKUP functions which can frustrate summary options as the only option available is SUM. This is likely to not be appropriate if the calculation is to incorporated into a complex function downstream where the summarising across time and up hierarchies means the FORMULA summary option is required. Chris

ChrisHeathcote

@takushimozuru Yes, it is possible to set a default time period based on another value. If you have not already created a series of time property modules this would be the best time to do so. We can use a a boolean formatted filter to set our default. The first time property module required covers the current time periods. Create a new module but do not add any dimensions, remove time and only include the following line items. Name of Module - Current Time Properties 1. Name - Current Period, Format - Time Period Months, Formula - PERIOD(CURRENTPERIODEND()) Create a second module but this time keep time as a dimension ( formatted to months ) and add the following line items. Name of Module - Month Time Properties 1. Name - Current Period Filter, Format - Boolean, Formula - Current Time Properties.Current Period The second module can now be used to filter any view in which you wish to only show the current period. Filters and be used to filter both page selectors and girds. Property modules can be invaluable when you need to filter and/or map across multiple dimensions be it time, lists or versions. Good luck, Chris

ChrisHeathcote

You are correct. I was not aware of formula scope and its range of functionality. I have managed to resolve the issue using formula scope to display actual and capture forecast in the same module.

ChrisHeathcote

It is unlikely that all users will require access to all the items in the list. Therefore, I would also suggest that you use filters to limit the view. If access is required then you can set up a series of filter booleans or list formatted line items to systematically allow the user to narrow down their selection.

ChrisHeathcote

Anaplan identifies a list item by code if the item has one ( all list items should be defined using a code ). Name is more of a convenient way of displaying and describing the list item. If you want to change the code you are effectively adding a new list item. Therefore, you must import or add the new code and then delete the old code. There is no way of changing the code via an action or process. WARNING !!! by deleting a list item you are also deleting all data associated with that list item. If you need to retain the data firstly create the new code and transfer data over before deleting the old code.

ChrisHeathcote

Hi, Line Items This can be achieved by using a Line Item Subset or LISS for short. Create this LISS and allocate the module containing the line items you would like to filter on. Create a new module and add the new LISS, users and time as your dimensions. Create a line item called selection and format this as boolean. On the dashboard or in the module to be filtered select this line item as your filter criteria. The user can then select the required line items from the new control module to limit or filter the line items in the target module. Time - All Periods All Periods can not not be selected directly. However, a time filter can be used which references a time properties module where this has been selected using a combination of formulas. Make sure the summary method is set to 'Formula' Use this line item to inform you time filter in the target modul. Hope this helps, Chris

ChrisHeathcote · ‎02-25-2020

Hi All, I need to be able to show actuals across a selection of line items which are populated by data sources and forecast inputs across the same selection of line items. My current solution is to use two modules containing identical line items with identical formatting using the same styles. This ensures they align when presented on a dashboard. However, as the model grows the user must update both modules when wanting to make changes to the line items which is more admin than I would like!! Is it possible to use a line item subset where the the style formatting is retained? I suspect this is not available, if so are there any suitable alternatives to avoiding the need to update multiple identical modules when making changes? This requirement just reeks of redundancy which I would like to minimise as much as possible. All suggestions welcome. Thanks, Chris

ChrisHeathcote · ‎02-21-2020

A quick through after looking at the screenshot. Would a creating a time dimensioned module along with all relevant dimensions such as employee, department and role with expenses data not satisfy your requirement. You could achieve this with the use of SUM function to combine into a third module and then pivot the views to display the data in any way you wish. This would only be possible this this is an output only view and does not require any user defined inputs.

ChrisHeathcote · ‎02-21-2020

I have not discovered a way to synch the scroll bar but you can remove the scroll bar entirely showing the full length and width of the modules. In the module edit side bar in design mode change the height and width settings to -1 as shown here ; This will lock the view and enable modules sitting side by side to align ( assuming the line items are listed in the same order and styled identically ) Where I have had to adopt this strategy I have used filters to limit the view to where the list of line items are long. If you are looking to adopt a similar strategy please let me know and I will lay out the steps I took to achieve it.

ChrisHeathcote · ‎02-21-2020

In the target module add the items list as a dimension. Try the following function; SampleA.Item [ LASTNONBLANK: SampleB.Item ] Please feedback any other solution you find. Chris

ChrisHeathcote · ‎02-09-2020

Hi All, I appreciate all your efforts to help. The solutions provided while appearing to offer a solution are all too lengthy and cumbersome where this can be resolved by redesigning the lists and removing the list subtotals. As this is a client model in which I have not been the principle model builder this will be my suggestion to the model team. Thanks

ChrisHeathcote · ‎02-07-2020

I get what @DavidSmith is suggesting but it seems a rather elaborate solution to quite a simple looking problem. It seems like there should be a simpler solution to hand. In terms of what I am trying to achieve it is a % calculation of the line / total for the list. The current solution uses SELECT but we are trying to remove these to allow the model to be more flexible and sustainable.

ChrisHeathcote · ‎02-06-2020

I have tried this approach using ITEM to create a mapping....this is not my issue. The problem comes from the fact that I can not use the parent column to lookup the parent. In this example it is London. LOOKUP will not return the value of London when used for child list items as it is not part of a composite hierarchy. This is because London is defined as a parent of the child items within the same list and is not defined as a parent within a composite hierarchy.

ChrisHeathcote · ‎02-05-2020

I have a list in which there are parent and child relationships defined. Ideally, these should have been created within a composite hierarchy but at this stage I am not considering redesigning the lists. Is there a function whereby I can reference one of these parent items? Example - Shoreditch, Westminster, Clapham are parents of London LOOKUP does not work as it only operates on composite hierarchies.

ChrisHeathcote · ‎02-05-2020

I would recommend using Dynamic Cell Access. Set up an Access Control module dimensioned as per the numbered list but also include users. Ensure the blueprint column, 'Show All Users,' is on. Create one line item called 'Write' and format it as boolean. Determine which list items each user requires access to and check the line item to grant access. Create a second line item called, 'Read.' Enter the following formula, NOT Write. In the target module enter the blueprint and scroll to the right to find the column, 'Write Access Driver.' Select the boolean line item from the new Access Control module for write. Do the same for 'Read Access Drive,' but select read. Each user should now only be able to access and write data against those items selected in the Access Control module.

ChrisHeathcote · ‎01-27-2020

A often used tool in my models is the ability to select a year via a boolean check on a dashboard. This line item is formatted as a year time frame which feeds a time property model to allow the user to filter for that particular year. Without YEARVALUE() the time property module, dimensioned by date or month time period will not know where to go to find the condition.

ChrisHeathcote · ‎01-10-2020

A possible solution which will allow you to maintain the tow grids is to synchronise the line items. Allow the user to browse from left to right in the left hand grid and if they required the YTD, FY19 or any time period they can select that column and the right hand grid will sych as appropriately. Firstly, re-pivot your right hand grid to place line items as a page selector and time period as columns. Use the show functionality to select the required time period aggregation; FY 19 and / or YTD. Ensure that line times are synchronised in the right hand grid and when the user selects a line item in the left grid the right grid should change accordingly.

ChrisHeathcote · ‎01-06-2020

The use of a concatenated unique identifiers adheres to best practice when creating mapping modules. However, the use of nested dimensions does work and will allow the user to map successfully across multiple dimensions without having to create a series of data transformations. I have used this approach successfully in my own models. However, try not to use more than one line item in order to limit the number of cells in the module.

ChrisHeathcote · ‎01-04-2020

Many of the answers are trying to crack a nut with a sledge hammer. Simply create a module dimensioned by Month time period, a line item called days in month, formatted as number and manually enter the days. This would take you no time and would be easy to maintain as the data will only change during leap years. You can then lookup from this table to feed any calculations.

ChrisHeathcote · ‎11-12-2019

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 · ‎11-12-2019

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 · ‎11-08-2019

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 · ‎11-05-2019

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