About ChrisHeathcote

ChrisHeathcote · ‎04-28-2020

Hi @Vladimir Using a mapping and LOOKUP function is the only approach that will allow you to reference a different item within the same list. When setting up the items in your list use a number reference as your code. In your system model recover the code for the current list item and convert to number from text. Create a rule to subtract the number of list items you need to retrieve data from. In your case as it is the previous item subtract 1. Use this result to FINDITEM of the corresponding list item. This will create the mapping you can use to retrieve data for any previous list item in that list. Chris

ChrisHeathcote · ‎04-27-2020

Using the list on a dashboard would not necessitate using two lists!!! You can filter the view using dynamic filtering fed by the boolean in the property module. The model is at risk of not be sustainable or auditable as the approach you are seeking to implement requires too many intermediate steps. But I see you have selected a solution so good luck. Chris

ChrisHeathcote · ‎04-27-2020

Hi, The active status of an employee is not a structure feature and therefore should not dictate the structure of the hierarchy. Instead, the active status is a property or attribute of the list item. Therefore, create a mapping in a properties module dimensioned by the employee list. Create a line item dimensioned as the active/inactive list. Alternatively, use a boolean format. And a date or time period formatted line item if you need to differentiate across time. You can use filtering and aggregation functions in downstream formulas to differentiate data across these attributes. This is a simpler approach, does not require an action and will keep sparsity to a minimum. Good luck, Chris

ChrisHeathcote · ‎04-15-2020

An alternative is to use selective access and only provide write access to those list items required by each user. They will only be able to see those list items allocated to them when setting them up as a user. When selecting which list items that are visible do not select the parent and only allow access to the child items at the hierarchy level required. Test this approach by limiting your own access to a selection from the list. Be mindful of NOT turning off you admin rights as you will not be able to reverse the change.

ChrisHeathcote · ‎04-15-2020

Hi, I think the best approach is to spread this out over multiple line items. The Quarterly summary is cumulative while the months are not and this is causing you the issues. Create the following line items - 1. Monthly 2. Monthly Cumulate 3. Cumulate reset 4. Ref 1. Monthly will house you monthly movement 2. Monthly cumulate will be the cumulative of the 1.Monthly, set summary to SUM, formula = CUMULATE( 1.Monthly, 3. Cumulate reset ) 3. Cumulate reset, boolean format and time set to Quarters, formula = true 4. Will contain a formula = 1, set summary to formula In line item 1. Monthly set your summary to ratio - 2. Monthly Cumulate / 3. Ref This will result in the your months showing the monthly movement and the quarters to cumulative for that quarter.

ChrisHeathcote · ‎04-15-2020

The simplest way to limit the view is to create a subset of the main list. You will need to create a subset for each incidence in which you wish to limit options. This approach will allow you users to see a limited view of the same list which will ensure downstream modelling is simpler.

ChrisHeathcote · ‎03-27-2020

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 · ‎03-27-2020

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 · ‎03-27-2020

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 · ‎03-26-2020

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 · ‎03-25-2020

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 · ‎03-24-2020

@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 · ‎03-24-2020

@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 · ‎03-09-2020

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 · ‎03-09-2020

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 · ‎03-09-2020

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 · ‎03-02-2020

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.