Quarterly computations

Business Central/NAV Developers Forum
1

Hi all,

Suppose you want to compute ‘Balance at Date’ quarterly (from GL Account) -In this case, 31/12/06, 31/03/07, 30/06/07, 30/09/07, 31/12/07- how do you ensure that the balance calculated is for the specified quarter.

I have used an array which calculates the dates correctly(OnPreReport). However, the array I use to calculate the Balances at Date (G/L Account - OnAfterGetRecord())gives me wrong figures! Below is the code Im using to calculate the balances:

Idx := 1;
“G/L Account”.CALCFIELDS(“G/L Account”.“Balance at Date”);

DateArray[Idx] := DMY2DATE(31,12,Year-1);
BalanceAtDate[Idx] := “G/L Account”.“Balance at Date”;

FOR Idx := 2 TO 4 DO BEGIN
DateArray[Idx] := CALCDATE(’+1Q’,DateArray[Idx-1]);
BalanceAtDate[Idx] := “G/L Account”.“Balance at Date”;
END;

Idx := 5;
DateArray[Idx] := DMY2DATE(31,12,Year);
BalanceAtDate[Idx] := “G/L Account”.“Balance at Date”;

Kindly assist me get it right.

Kind rgds.

2

Hi Ruthdeshy,

You need to do 2 things…

  1. You need to assign proper datefilter to G/L Account, everytime you calculate the DateArray-variable. (e.g. “G/L account”.SETFILTER(“Date Filter”,’…%1’,DateArray[Idx]))

  2. You need to the CALCFIELDS every time you need the balance.
    What you do is calculate the “Balance at Date”, without any filters at all, and then reuse that balance in every collumn.

So you need to put 2 extra lines in between the line where you calculate DateArray[Idx], and the line where you assign a value to BalanceAtDate[Idx].
e.g.
DateArray[Idx] := DMY2DATE(31,12,Year-1);
“G/L Account”.SETFILTER(“Date Filter”,’…%1’,DateArray[Idx]);
“G/L Account”.CALCFIELDS(“Balance at Date”);
BalanceAtDate[Idx] := “G/L Account”.“Balance at Date”;

3

Thanks Big!!

It now works as desired!

Regards!