topic Rolling Monthly Data Fields in QlikView

Rolling Monthly Data Fields

asmithids — Tue, 29 Dec 2015 14:49:27 GMT

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 — Tue, 29 Dec 2015 14:56:56 GMT

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

asmithids — Tue, 29 Dec 2015 15:08:05 GMT

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_periasamy — Tue, 29 Dec 2015 16:03:08 GMT

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

Re: Rolling Monthly Data Fields

asmithids — Tue, 29 Dec 2015 16:58:01 GMT

Thank you Settu,

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

Thank you very much for your help!!!

Re: Rolling Monthly Data Fields

asmithids — Wed, 30 Dec 2015 15:24:14 GMT

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!

Re: Rolling Monthly Data Fields

settu_periasamy — Wed, 30 Dec 2015 15:43:21 GMT

Good to know.. You are Welcome!