Rolling Monthly Data Fields

[asmithids]

Hello,

I am developing a balance sheet dashboard based on the Monthly Data Bucket fields below.  The data fields are rolling values.  Meaning, based on the naming convention of the fields, the period reference remains the same (i.e. CurrYr_Jan, PriroYr1_Jan) however, the transaction Month-Year will change. 

For example, Jan-2015 = Balance_CurrYr_Jan and Jan-2014 = Balance_PriorYr1_Jan.  Then when the calendar crosses to 2016, Jan-2015 becomes Balance_PriorYr1_Jan and Jan-2014 becomes Balance_PriorYr2_Jan and so on across all periods through PriorYr3. 

I have a transaction date field that I am using as the master calendar that includes the following date fields

• MM/DD/YYYY
• MM-YYYY
• Month
• Year
• Quarter End

I am not sure how to code the load script to create such a cross-reference table that will roll as the years move forward.

Thank you in advance for any assistance on this development. 

Monthly Data Buckets:

Balance_CurrYr_Jan,

Balance_ CurrYr _Feb,

Balance_ CurrYr _Mar,

Balance_ CurrYr _Apr,

Balance_ CurrYr _May,

Balance_ CurrYr _Jun,

Balance_ CurrYr _Jul,

Balance_ CurrYr _Aug,

Balance_ CurrYr _Sept,

Balance_ CurrYr _Oct,

Balance_ CurrYr _Nov,

Balance_ CurrYr _Dec,

Balance_PriorYr1_Jan,

Balance_PriorYr1_Feb,

Balance_PriorYr1_Mar,

Balance_PriorYr1_Apr,

Balance_PriorYr1_May,

Balance_PriorYr1_Jun,

Balance_PriorYr1_Jul,

Balance_PriorYr1_Aug,

Balance_PriorYr1_Sept,

Balance_PriorYr1_Oct,

Balance_PriorYr1_Nov,

Balance_PriorYr1_Dec,

Balance_PriorYr2_Jan,

Balance_PriorYr2_Feb,

Balance_PriorYr2_Mar,

Balance_PriorYr2_Apr,

Balance_PriorYr2_May,

Balance_PriorYr2_Jun,

Balance_PriorYr2_Jul,

Balance_PriorYr2_Aug,

Balance_PriorYr2_Sept,

Balance_PriorYr2_Oct,

Balance_PriorYr2_Nov,

Balance_PriorYr2_Dec,

Balance_PriorYr3_Jan,

Balance_PriorYr3_Feb,

Balance_PriorYr3_Mar,

Balance_PriorYr3_Apr,

Balance_PriorYr3_May,

Balance_PriorYr3_Jun,

Balance_PriorYr3_Jul,

Balance_PriorYr3_Aug,

Balance_PriorYr3_Sept,

Balance_PriorYr3_Oct,

Balance_PriorYr3_Nov,

Balance_PriorYr3_Dec,

[sunny_talwar]

Are all these fields with multiple rows of data you are looking to create or will Balance_Prior_blahblah will always have one value for each one of them???

[asmithids]

Hello Sunny,

Each field contains one value per GL account number.

For example:

GL Account     Balance_CurrY_Jan      Balance_CurrY_Feb      Balance_CurrY_Mar      etc...

1000                         $1234                              $5678                              $9012

1500                         $4565                              $8765                              $6567

2000                         $2346                              $9268                              $2347

2500                         $7659                              $4568                              $8768

[settu_periasamy · Accepted solution]

Hi,

May be like this..

CrossLoad your Table, Then use the Mapsupstring function to replace the value.. Like

T1:
CrossTable(YearMonth, Data)
LOAD GLAccount, 
     Balance_CurrYr_Jan, 
     Balance_CurrYr_Feb, 
     Balance_CurrYr_Mar, 
     Balance_CurrYr_Apr, 
     Balance_CurrYr_May, 
......
....
From Source;

After Cross Load..

MAPField:
Mapping
 LOAD * INLINE [
    F1, F2
    Balance_CurrYr_, 2015
    Balance_PriorYr1_, 2014
    Balance_PriorYr2_, 2013
    Balance_PriorYr3_, 2012
];


T2:
LOAD GLAccount,Data,MonthName(Date#(Left(MapSubstring ('MAPField', YearMonth),7),'YYYYMMM')) as MonthYear Resident T1;
DROP Table T1;

Check the Attachment. May be helps..

[asmithids]

Thank you Settu,

I will test this and will let you know the result.

Thank you very much for your help!!!

[asmithids]

Hello Settu,

Your solution worked very well.  Thank you!!  I created a balance sheet and are pulling correct balances per GL account per period.   

Thank you again!

[settu_periasamy]

Good to know.. You are Welcome!