Balance accounting tables, accumulations and missing data. Can you help ?

[#1]

Dear all,

I need to process a table with account data with the following structure:

ENTITY, MONTH, YEAR, PARCEL_CODE, VALUE

Every month the table is load with new records of the combined ENTITY, MONTH YEAR, PARCEL_CODE, and VALUE that happened in the current month. So in any point in time the input table holds all the "movements" from 2004 to date.

By the nature of the operations, most of the accounts do not have movements every month.

I've got two problems:

1^st problem: I would like to have a simple solution that, Chosen an ENTITY and a range of dates, displays a table with the accumulated VALUE since 2004 for each PARCEL_CODE .

I was unable to have this to work with a pivot having PARCEL_CODE and MONTH-YEAR as Dimensions and sum (VALUE) as expression. The problem is that we need to show accumulations since 2004 (ORIGIN) and I couldn't find a condition that solves this problem.

So I calculated the accumulations based on a synthetic key of ENTITY& YEAR& MONTH&PARCEL_CODE on the load script and it works very well, but..

2^nd Problem: accumulations are calculated only when there are "movements" in the account values: say that for a given ENTITY and PARCEL_COD there were a movement in Jan and another one in Aug, than only Jan and Aug will have also de accumulated sum of movements.

But, If a user wants to see the accumulated value from Feb to Jul per month, the table should show the Jan value (which is the true value!) for each month, and I can't find a way to do it, since the pivot table would simple show missing for those months.

I'm really stuck with this.

Thanks in advance for any solutions or suggestions,

Best regards,

pmb

[johnw · Accepted solution]

If I've understood the situation, add a MONTH_YEAR to your original table, then generate an AsOf table like this:

AS_OF_MONTH_YEAR, MONTH_YEAR
Jan 2004, Jan 2004
Feb 2004, Jan 2004
Feb 2004, Feb 2004
Mar 2004, Jan 2004
Mar 2004, Feb 2004
Mar 2004, Mar 2004
etc.

In other words, just link every month to itself and every prior month. Now you use AS_OF_MONTH_YEAR instead of MONTH_YEAR when you want an accumulation, and a simple sum(VALUE) should give the right total, as it will automatically link to that month and all prior months.

Another answer is to generate the missing months of data for every entity and parcel code, and do the sum in the script like you said, but since most accounts do not have movements every month, that feels wasteful to me. It also wouldn't respond to certain user selections the way I'd think you'd want.

[#3]

Thank you John, seems a god idea. By the way since we are working with a range of 39 years, do you know of any means for generating such a table ?

Best regards,

Paulo

[#4]

Apologies John.

I've already found an example written by you in http://community.qlik.com/forums/t/32982.aspx

I'll adapt it and give it a try.

Best regards,

Paulo