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!
Answers
-
Hi
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?
BR
Tingting
0 -
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.
1 -
0
-
Ok, I will try. Thank you!
0 -
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,
Dominika0 -
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.
0 -
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,
Dominika0 -
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.
0
