Calculate Debtors age wise oustanding

[vikasmahajan]

Dear all

I have following table  along with  invoices and there payments which are in negative values

Cust Code	Doc_no	Doc Date	Amt
A00001	S00001	01/04/2013	12000
A00001	S00001	12/05/2013	-1000
A00001	S00001	30/06/2013	-6000

My requirement is calculate age wise outstanding as on date  on following criteria

When User Select Date As on		o/s will be
12/05/2013		11000
01/07/2013		5000

Please help me arrive the same  excel sheet is attached for the same.

Thanks in adv

Vikas

[Sokkorn]

Hi Vikas,

You may try this script

[Data]:

LOAD * Inline [

CustCode, Doc_no, DocDate, Amt

A00001, S00001, 01-04-2013, 12000

A00001, S00001, 12-05-2013, -1000

A00001, S00001, 30-06-2013, -6000];

[Data2]:

LOAD

  [CustCode] AS [CustCode2],

  [Doc_no] AS [Doc_no2],

  [DocDate] AS [DocDate2],

  [Amt] AS [Amt2],

  RangeSum([Amt], Peek('Amt3')) AS [Amt3]

RESIDENT [Data] ORDER BY [DocDate];

Regards,

Sokkorn

[Sokkorn]

And you may look into this doc also.

[vikasmahajan]

Thanks Sokkorn Cheav

I Am Working on it I will get back you.

Vikas