How to convert flat data into monthly - GL example

jakesachs

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. 

GL_Data_Question_112278.PNG

LipChean_Soh

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'

 

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

.   .      

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

 

 

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.

 

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