About ChrisHeathcote

I have recently built a variance report which takes this further. By using two sets of LISS; one for the headers such as Actual, Forecast, Act v For etc and another for the report line items you are able to create a situation where there is no need to use LOOKUP and whereby you can incorporate ratios such as margin % and other KPIs into your variance analysis.   Create a report calculation module and add the header line items that you would like to see in your report. Create a LISS containing all the header line items. Lets call this REP01 : Report Calc Create a report module adding the LISS from step 1 and list all the required line items that you would like to include in the final report. Create a LISS for all line items. Lets call this the REP02 : Report Module. Return to the REP01 : Report Calc module and add the LISS from REP02 as a dimension.  Now working in the REP01 : Report Calc module map all the relevant data into each line item; Actual, Forecast, Budget, Prior Year etc. Also, add formulae to calculate the variances.  If you have multiple versions of each header such as multiple budgets or forecasts create flat lists for each and add these to the module Applies To. If space is a factor remove these dimensions from all other line items except those that reference them directly or are used in the variance calculation. A number of subsidiary line items will be created but the user will be able to select the relevant versions of both budget and forecast via a page selector so the enhanced user experience in this situation outweighs any inconvenience of have these in place. Switching back to the REP02 : Report use COLLECT() to pull through all those line items that do not require a calculation. For items such as Profit, Margin etc that require a calculation enter these and use IF THEN ELSE to check whether the header LISS is a variance. Set up a systems module and use a boolean to query whether the corresponding LISS is a variance and set up the formula to calculate accordingly. Example, if you use positive variances to indicate favourable performance and negative variance to represent adverse performance you will need to flip the Profit calculation from revenue - costs to revenue + costs for LISS items that are variances. If you are including ratios in the report use a series of nested IF THEN ELSE functions to query the nature of the variance using the systems module set up in the previous step. For each variance such as Act v For, Act v Bud, Act v PY etc reference the relevant line items in the report and use SELECT() to isolate the LISS item required to calculate the corresponding variance. Close out the IF THEN ELSE with the final calculation where the value for non variance headers are finally calculated.  For all the calculated line items change the summary setting to FORMULA. This will ensure that the ratios calculate correctly up and across any dimensions. This using COLLECT() can be set to SUM.   By not using LOOKUP we are able to utilise formula as a our summary method and allow for the calculation of ratios up all relevant hierarchies. The use of two interconnected LISS also simplifies the collection of data into the final report and allows the the isolated calculation of each header in the report calc module. By adding additional lists which allows for multiple versions of a key comparator the user is able to select the relevant budget, forecast or any other comparator. This also means that we do not need to add the users list.    In my example I also added currency type which allowed the user to convert the report between three different currencies which are set as properties of one of the key dimensions. This list was small consisting of; local, reporting and group, but demonstrates that the reporting can be built up using a number of key dimensions that all the user to view their report across a variety of different views.    I am still developing the report and would therefore love to hear others thoughts on the above and whether the approach is sound.    Thanks,    Chris Heathcote  ... View more

@u381725    Your mapping needs to be a list formatted line item which bridges the gap. If you are using a LOOKUP then it needs to be the same format as the source dimension which is missing from the target. The dimensions of the mapping module must be present in the target too.   Chris  ... View more

@u381725  From the screen shots you provided it is not possible to see the dimensions of either the source or target modules. An error is likely to be a mismatch of dimensions meaning that you need to map any differences in dimensions from the source to the target.  Therefore, list out the dimensions present in your source and those in the target and identify any differences. Time - is there a change in the time dimensions from year to month, month to day etc? Hierarchies - is the target set at a higher level in the hierarchy? Missing dimensions - are there any dimensions that are simply missing in the target that are present in the source or vis versa? Versions - does one module have versions and the other not? Use system modules to map across these differences in dimensions. For time you could use MOTHVALUE() or YEARVALUE() or LOOKUP within a time systems module the relative parent. Similarly, with a movement down a hierarchy use a systems module dimensioned by the list present in the target to LOOKUP the parent present in the source.   Where there a simply missing dimensions build dedicated mapping modules containing the list present in the target. Use line items formatted as the target dimension that is missing and map each combination of target dimensions into the source list.   Where there are more dimensions in the source use SUM to aggregate the source data into the corresponding target dimension. Create a module dimensioned by the source lists and use a list formatted line item formatted to the list in which you will SUM the data into.    Good luck,   Chris  ... View more

@Star  The first period of the year is effectively a property of the year time dimension. Therefore, create a module only dimensioned by time dimension and formatted as year.  Create a line item for each time period you want to reference and ensure they are formatted for that time period; months, Qtrs etc Use the following formula = period(start()) The formula above so default to the period at the start of each year in the calendar. In your target time settings module reference the above line items using the following. =YEARVALUE(YearTimeProperties.First Period) = ITEM(TIME) This will check the first period matching the dimension of the target module and the Year time properties.   As Anaplan does not have a native week time scale that can be used as a time dimension you will need to map the first week into the year manually in your year time settings module. Then instead of referencing Time with an ITEM function you will reference the weeks list.   Good luck,   Chris ... View more

If the MAX needs to reference the full year then you can not use zero as the end reference in the MOVINGSUM as this will move with the calculation. Using Zero will only bring the calculation upto the current period. While the start is -Month, end would be 12-Month if you are to capture the full 12 months for each period. ... View more

@nishantgarg  Yes, this is possible. The trick here is to create a filter module dimensioned by your target list, along with a subsidiary view dimensioned by the parent and a ratio summary setting.   Create the following line items; Notice that I have used a subsidiary view for the line item Parent Item. This has had its dimensions changed to the parent of Level 3, Level 2. Also, notice how the summary option for Parent Filter Ref is ratio. The final filter line item, Parent Filter Boolean is Parent Filter Ref = 1 and the summary set to formula.  This set up will check all parents rolling up the hierarchy. If you only want the immediate parent, one level up the hierarchy to be checked change the summary option of Parent Ref to Sum.   Good luck,   Chris      ... View more

@saurav4ss  Conditional formatting in the UX uses a line item number reference to determine what colour to use.  Therefore, create a new line item in your target module and using IF THEN ELSE create a condition in which a +1 is returned when WFC > 6 and a -1 is returned when WFC < 1. I would also caution as to what happens when WCA = 1. Therefore, use a statements similar to the following; = IF WCA > 6 THEN 1 ELSE IF WCA < 6 THEN -1 ELSE 0 Zero is return if neither conditions are met. In the UX when setting up the dot chart use this line item to inform the conditional formatting. Good luck, Chris ... View more

@litterbarbie  Firstly, avoid using TIMESUM when the target is dimensioned by time. This function should only be used when the target contains no time dimension. In you your case we should use MOVINGSUM as the target contains a time dimension. From your query I would suggest that the the source and target line items are not using the same format. Note, that if you are to use MOVINGSUM the start and end period must be numbers which are relative to the time period. Therefore, you will need to calculate these in a time systems model to ensure that you cover the full time period under review. To achieve this create three line items in the month time systems module all formatted as number; Month Count = IF 1 + PREVIOUS(Month Count) > 12 THEN 1 ELSE 1 + PREVIOUS(Month Count) Start Month = 12 - ( 11 + Month Count ) End Month = Start Month + 12 Example - March, start month -2 = 12 - ( 11 + 3 ), end month 10 = -2 + 12   Use these line items to inform your MOVINGSUM function.   Good luck,   Chris ... View more

@neg177  You are most likely have issues with mapping your time dimension. A few things to consider;   Use a custom time mapping where you specify which part of the time header is day (d), month (m) and year (y). At first glance attempt DD/MM/YY Remove the first column from you data as it will cause your mapping to return an error as the import will try and match this to a time period. If you can make your time reference another column and map this into time instead of using the column headers option. Try a few interations of the time mapping  ... View more

@Rahul_Rathod  To elaborate on @JaredDolich point. Any manual manipulation of the view will void the synchronisation. These could be showing or hiding rows/columns or filters. The manual formatting will take priority. Can I also confirm that the context selectors are lists and not context filters.. These do not synchronise with each other. Chris  ... View more

@DeveloperCYT  You only need to add the users list to the module used to create the filter not the final output.   Create a module dimensioned only by user and two line items, one dimensioned by customer list and the other by sub brand list. This is your filter selector. Create a module dimensioned by customer and sub brand containing the users list, create three line items; customer filter, sub brand filter and final filter dimension these as boolean. This is the filter module. Reference the filter selector to determine if the dimensions equal those selected. Customer Filter = IF ISBLANK(FilterSelector.Customer) THEN TRUE ELSE Item(CustomerList) = FilterSelector.Customer Sub brand Filter = IF ISBLANK(FilterSelector.Sub) THEN TRUE ELSE Item(SubBrandList) = FilterSelector.Sub Brand Final Filter = Customer Filter AND Sub Brand Filter   Apply the final filter line item as the filter criteria to you output module. You do not need to add users to this module as you can select 'Current User,' when defining the filter criteria.   Chris      ... View more

@DeveloperCYT  It is possible to allow users to select a unique view. Ensure the module used to create the filter and the module used to allow the selection contain the user list. Set the summary to any. When adding the filter to a target view enure that 'current user' option is selected. The final outcome is that the view will be filtered as per the criteria that user. ... View more

@DeveloperCYT  To allow the selection of the top level these must be included in the filter query. Set the summary option in the filter line item to any and the summary should become available. ... View more

@Adisulyadi97  It is not possible to combine a formula and a user defined input in the same line item. As described by @ArunManickam  version scope is the only instance where this is possible where you can define which version is subject to a formula and which is driven by user inputs.  The best practice here is to create multiple line items; one to capture the formula, another to contain the user input and a third to combine them based on your logic of which takes priority.  The final line item can then be used in downstream processes.   Good luck,   Chris ... View more