Convert Line Items to Time Periods
Our Finance team requested we try to recreate this view in Anaplan for them to make their plans. We are struggling with how to show both the detailed info (vendor, department, country) and the time periods (Jan 22, Feb 22 etc) as single rows in the same table.
Our best workaround so far is to include the time periods as line items, creating line items for Jan 22 through Dec 22 then displaying all line items as columns. The issue is that we do not know how to then convert these into time periods for other modules.
End goal is that we can display the line item view described above, then have separate modules that take the line item months and sum them into the corresponding time periods. Any help is greatly appreciated!
Best Answer
-
Hi,
I am still sure that it can be done in nice and clean way (0 maintenance cost!), so I prepared small demo:
@alexpavelI think you slightly misunderstood me: I proposed to use filter to show only 2022 (or generally current year periods), but to also use hide/show to only show one column for properties, but multiple for Value. How it looks like:
NUX (using custom views and just-released ability to Show/Hide there on nested dimensions):
Classic UX:
@AlexGSGInstructions how to do it (I am also attaching a video with walkthrough of the 3rd point):
1. Blueprint of main model:
2. Blueprint of the filter that needs to be applied to view to get desired results (I assume you already have some other line item that determines what is current year that should be shown, per your needs):
3. Then, when you are building the view in NUX, apply filter, and then:
HOLDING CTRL KEY click on "All Periods" (lower columns header row) for all properties columns and on "Value" (upper columns header row), then right-click and select option "Show->Selected". We are almost there -the only other thing you need to do is to hide "All periods" for "Value" line item: right-click at it and select option "Hide->Selected".6
Answers
-
Hi,
If you really want to use line items as periods, and later map it to Time dimension, create Line Item Subset, add your "months" line items to it, and then in new module you can collect it and map to Time dimension.
But I'd firstly ask myself a question, if this architecture won't be too complicated and hard to maintain - maybe it would be easier to define three line items: Department, Country and Values, where all have dimension of rows, but Values also have dimension of Time, the same as whole module. Then you can have two dimensions in columns (Line Items and Time) and filter it/select in a way that you only select one occurrence of first two (properties) line items, but show full year for Values.0 -
It s easier to add line items that repeat your module dimensions names as columns and use the standard time dimension rather than the other way around.
Showing dim items as column line items is quite easy to achieve with the use of : item(dim name) and the line item formated as a list corresponding to the dimension you are trying to show.
The use of a set of display filters will allow to show the 3 dim columns on the "all periods" time member only.
0 -
@AlexGSG It is always tricky when it comes to this layout: to publish all 12 months + some property columns.
@M.Kierepka It would be really nice to be able to create a dynamic layout using Line-item and Time dimension in columns area and use the filers to show the property columns only at total year. However, currently, this layout cannot be achieved dynamically.
The filter should be built on the intersection of the line-item Department (or other property line-item) with Time list. In order to create line-item filters, it is needed to have a line-item subset.
Unfortunately, the line-item subsets cannot be created for line-items that are not formatted as number and Department line-item cannot be formatted as number.
With quite a sweat, the layout can be manually built in a saved view in the classic interface, but it is static and next year it is needed to be rebuilt. In the NUX page you are obliged to publish a saved view (not custom-view) that does not have all the formatting capabilities as NUX custom views.
There is an idea exchange about allowing the non-numeric line-items to be accepted in line-item subsets:
@AlexGSG An example of how to convert from Line-item to Time list:
1. In Input module create 12 additional line-items formatted as Time. You do not need to have in "Apply to" anything. This way you will decrease the nr. of cells.
2. In the module with Time dimension, create the formula to add all 12 months:
And this will produce the correct monthly values:
Hope it helps
Alex
0 -
Thanks Alex, we'll try this out and let you know if it works!
0 -
Amazing! 🙂
I was aware of how to build static views, but I did not figure out how to make it dynamically.
Really helpful!
Thanks for sharing!
Alex
0 -
This answers my question, thanks! In the UX Page we made one grid without time for them to enter the details, then a second grid underneath as you describe below. Very helpful!
1
