6 Replies Latest reply: Dec 30, 2015 10:43 AM by Settu Periyasamy RSS

    Rolling Monthly Data Fields

    Alec Smith

      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,

        • Re: Rolling Monthly Data Fields
          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???

            • Re: Rolling Monthly Data Fields
              Alec Smith

              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

            • Re: Rolling Monthly Data Fields
              Settu Periyasamy

              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..