3 Replies Latest reply: Nov 3, 2010 5:20 AM by Paulo Barbosa RSS

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

    Paulo Barbosa

      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:

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

      2nd 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