Creating a new model - issue

I got stuck at one point in building a new module and I thought maybe someone could give me some advice.

1. I built an INPUT MODULE that has a lot of line items (about 30) - some of them are filled uploaded file and some are calculated based on other line items. For this INPUT MODULE data is loaded for two version separately (Forecast, Budget).

Now I'm moving on to where I'm stuck:

2. I need to create a module for this OUTPUT in which I will present the same dimensions, line items as in the input module, but in addition I need to calculate the variance between the forecast and budget value (see the attached photo to visualize)

My ideas:

Create a Line Item Subset, and in the output module new line items (Forecast, Budget, Variance), but I don't know how to get the values for the corresponding Line Items Subset items from the input module. Maybe using LOOKUP? COLLECT?

Thanks in advance for the hints!

Accepted answers

All comments

tingtingxia

1. Your OUTPUT module screenshot is missing in your post. Can you help to provide it again?

2. Your "version" is model version or self-created list of version?

Tingting

ChrisAHeathcote

@Dominika_Dudzinska

You are correct in that using a line item subset (LISS) is part of the solution.

In you new output module add the LISS as a dimension without versions.

In the input module create a new line item and add the LISS to the applies to in addition to all other dimensions.

Use COLLECT() to collect all the data held in the corresponding line items.

In the output module use SELECT() to reference this new line item and select the corresponding version data;

For the actual line item use;

=InputModule.LISSLineItem[SELECT:Versions.Actual]

For the actual line item use;

=InputModule.LISSLineItem[SELECT:Versions.Budget]

The above assumes you called you budget version 'Budget'. If it is called something slightly different use that name instead. The important thing is that it references the version by name that you would like to pull data from in you input module.

For the variance simply use;

=Actual - Budget

There is a crucial requirement for this approach to work. All the data in the input and output modules must be number format. LISS do not work for non number formatted line items.

Dominika_Dudzinska

@tingtingxia

Self-created lists of version, I attached the missing photo.

Dominika_Dudzinska

Ok, I will try. Thank you!

Dominika_Dudzinska

Hello @ChrisAHeathcote

I am not sure what I do wrong, but when I added additional line item in the INPUT module with formula COLLECT(). I have zero values inside.

Does Applies To with LISS (included line items in the list) should be added general to the module or only for the new line item?

Best regards,
Dominika

ChrisAHeathcote

The line item with the collect() formula will create what is called a subsidiary view. You will not see any values until you access the view by clicking on the little table icon next to the line item name.

This line item should be the only line item where you add the LISS. If you try and add it to the module you will create a circular reference and generate an error.

Dominika_Dudzinska

Tank you for your help.

How to keep the data format from LISS, I mean some of it is % and some is normal number. And when I set the line item where the data should go, I can only set it in one format. How to work around it?

Best regards,
Dominika

ChrisAHeathcote

The solution above does not allow you to mix the formatting across rows and columns as formatting is editable only within a line item. The LISS effectively converts your line items from the target module into a list.

A work around here is to use two interdependent LISS.

Create an additional module using your LISS from module 1. Add line items for Actual, Budget and Variance.

However, instead of using COLLECT() map your data into this new module using the LISS from module 1 to map data in from the various sources for Actual and Budget. Calculate the variance as before. Now create a second LISS from this new module.

Add this new LISS to module 1 and remove native versions.

Use COLLECT() here to pull the data in from module 2.

You will now be able use different formatting for each line item.

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385