How to convert flat data into monthly - GL example

jakesachs
jakesachs
in Integrations and extensibility

Confused on how to work with this flat data and derive monthly values from it. Appreciate the help!

 

Each row is a unique combo of Account (GBAID), Ledger Type (GBLT) and Year (GBFY). The snippet attached is 4 entries for the same account and ledger type for 4 different years. GBAN01-12 are entries by month. For example, GBAN11 for the row for 2018 would be the value in November 2018, the 2021 row only has values through GBAN05 aka May because we have not closed Junes books yet (today is June 23). 

 

I want to be able to separate these entries by month in another module dimensionalized by time so I can track entries for any account combination over time. For example, a module could be dimensionalized by Account and Time/Months and each month's value would be a combination of its GBAN and GBFY in this dataset. 

 

Thanks for your help. 

Tagged:

Best Answer

  • LipChean_Soh
    LipChean_Soh
    Answer ✓

    Hi @jakesachs 

     

    The format of the GL records is not ideal, the better way of doing things is to follow 

    https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-Peak-Performance/ta-p/48866

     

    Pls scroll to the area that says 'Good Way'

    LipChean_Soh_0-1624497178095.png

     

    Having said that, pls see if the following works for your current data format:

    LipChean_Soh_1-1624497232307.png.   LipChean_Soh_2-1624497251699.png.      LipChean_Soh_3-1624497273025.png

    1. I created '99999999' in GBAID as a dummy member. Same goes for 'ZZZ'.

     

    LipChean_Soh_4-1624497338740.png

    LipChean_Soh_5-1624497390813.png

     

    2. I only created the relevant line items along with 3 months worth of data, so you can expand accordingly.

    3. I avoided using FINDITEM for Jan, Feb, Mar because it would mean a lot of slow calculations repeated 12 times.

    4. I used 'Jan+2' instead of 'Feb+1' for Mar, to avoid daisy chaining.

     

    LipChean_Soh_6-1624497528223.png

    5. This formula needs to be extended once you included Apr-Dec data columns in the 'GL Records' module above.

    6. While this works, you can also see the additional amount of SUM we have to use (12 times more). This is not good for the performance and sustainability of the model.

     

    Thanks,

    LipChean

     

     

Categories