Mapping Data into a Dimension
Hi Community,
For a UX Dashboard, I'm trying to have some functionality where the end user can choose a year and a version and it would then populate that data from Jan-Dec in front of them. I'd ideally like them to be able to then choose another year and version and to have both compare to each other.
For example, if they were to pick Budget 2020 and Actuals 2019, it would show all the Jan-Dec data for Budget 2020, all the data for Actuals 2019 below it (perhaps in another grid) and then a grid with the variances.
To do this, I thought about having a module where they could have a dropdown to choose these 4 variables (Year 1 Version 1, Year 2 Version 2) and to then have some form of lookup, i.e. if 2020 is chosen, lookup Jan 20 and return in the Jan column. Does anyone have an idea of how this could work and what formulas I could use? I'm struggling to take the data (i.e. Jan 20) and to have it lookup into a dimension on the module where the actual data is stored.
Thanks all!
Answers
-
Hi,
Lookups with versions in Anaplan can actually be quite tricky as you can't set a line item format to the native version list. If you are using a Version list it will be more straightforward. However if not don't worry this blog post should give you the steps you need to take:
0 -
Hi, a few pointers and ideas to help for navigate your way through this challenge.
- You will need to create a list of months and map these into Anaplan time using a time settings module. This is so you can direct the aggregation function to the relevant month
- You will need to create a versions list and map this to the Anaplan native versions. This is to inform the mapping and to allow the user to select which version. Create a module dimensioned by versions and using a single list formatted line item map these directly into each corresponding list item in the versions list.
- Create a selection module containing no dimensions but enough line line items to full define the selection; one formatted as the versions list from above and the other as year time period. These will be used to determine what data is pulled through into the report.
- Create a data module where you will collect all the relevant data as per your users selections.
- Dimension this by the months list and versions list created in the previous steps. Add all additional dimensions within the source that you would like the user to be able to further analyse the data.
- Create three line items; version 1, version 2 and variance.
- Using the selection module define what data is pulled in;
- Firstly, use IF...THEN...ELSE to determine which year. Use a time settings module to determine which year has been selected. A module dimensioned by time period month and a boolean formatted line item for each selection. For example, version 1 and version 2 will require a line item each if you wish to report each across a different time period. Create a seperate line item called year and format this as time period year. Use =PARENT(ITEM(TIME)) to populate this line item with the relevant year for that period. In you boolean line item use =Year = Selection.Year. If the selection equals the year of that period it will be checked.
- Secondly, use SUM to aggregate all the period data from the source into you target data module as per the mapping set up in your time setting module
- Thirdly, use LOOKUP to determine which version you would like to pull in as per the selection
- Combine these to create a formula for version 1 and version 2 which allows you to specify the data as per your selection.
- The formula should like something like; IF time settings month.version 1 THEN source data[SUM:time settings month.month list mapping, LOOKUP:User selection.version 1 selection] ELSE 0
- The above would be used for the version 1 line item and you would replicate this for version 2. The variance line item would be a simple calculation between the two.
Post here as to how you get on with the above approach. I have used this on many occasions and it works well.
Good luck,
Chris
0
