Add columns to modules with differences and ratios and between years

ManuelSampaio
edited November 2022 in Modeling

Hi everyone,

 

I have I doubt which is if it is possible to add a column or more to a certain module in order to know for example the difference between the revenues in FY20 and F21 or a ratio that shows how much it grew from one year to the other.

 

ManuelSampaio_0-1669140565057.png

 

In this specific example I want to create something as can be seen bellow with 2 new columns named difference and variation which it is just the difference between the 2 years and the variation on these line items.

 

ManuelSampaio_1-1669140730159.png

 

Can anyone help me?

 

Tagged:

Answers

  • Misbah
    edited November 2022

    @ManuelSampaio 

     

    There are multiple ways to do so: Here are a couple of options that i can think of

    Option 1: 

    1. Create a Line Item Subset (LIS) based off of Example Module and pick all the three lines in your LIS.

    2. Create another module, Module 1, dimensioned by LIS and Country and Time. Use COLLECT() function to pull the data from Example module to this module.

    3. Create,Module 2, dimensioned by LIS and Country. There should not be Time dimension in here. Insert four lines in this module - FY20, FY21, Difference  and Variation. I would recommend Naming FY20 and FY 21 as general time periods like Current Year, Previous Year etc. Use Module 1 as a source module and pull the data from module 1 to module 2 using LOOKUP function. For example 

    FY20/Previous Year line item = Module 1. Data line item[LOOKUP: SYS Global module.Previous Year]

    Note: Here Global Module is a System module with no dimensions and having a line item called Previous Year or FY 20  which is formatted on Time(Year) and manually select FY20 in it. Similarly there will be another line for Current Year or FY21.

    Finally you can calculate difference and variation lines

     

    Option 2: 

     In Report Pages of UX, there is Variance Analysis functionality with no additional development required as it needs to be done in option 1.

     

    Misbah_1-1669213739175.png

    Misbah_2-1669213819883.png

     

    Thanks,

    Misbah

    Miz Logix