Aging of invoices

[#1]

Hi All,

Can I ask a little help regarding my report.

I'd like to get the aging of all the unpaid invoices and segregate them according to aging, 30 days, 60 days , 90 days and beyond 90 days excluding weekends and holiday. Below is/are the following field name:

Entry Date - this is the starting date

07.22.2014 - current date or end date

Thanks!

-Ahyel

[#2]

Apart from QV script, practically I have never seen excluding off days for ageing.

[vikasmahajan]

In Calculated Dimension create expression like following will create buckets

=if((num((num(vDateForAgeing)-[Due Date CLE]),'#######0') )<=0 ,'Not Due',

if(IsNull([Due Date CLE]) OR [Due Date CLE]='','Not Due',

if((num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))>=0.1 and (num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))<=30,'1-30',

if((num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))>=30.1 and (num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))<=60,'31-60',

if((num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))>=60.1 and (num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))<=90,'61-90',

if( (num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))>=90.1 and (num((num(vDateForAgeing)-[Due Date CLE]),'#######0'))<=180,'91-180',

if(num((num(vDateForAgeing)-[Due Date CLE]),'#######0')>=180.1,'Above 181',

0)))))))

Where VDateForAgeing will be Variable -> =IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(PostingDate))),DATE(Today()))

Using this you can get current ageing as well as   as-off  date ageing.

Hope this helps you.

Vikas

[#4]

I assume Starting Date = [Doc Date1] and Unpaid Amount= Receivable. You can add expressions as below:

1. Days Overdue - =if(Receivable=0,0,Today()-[Doc Date1])

2. Current -            if([Days Overdue]=0,Receivable,0)

3. 0-30 - =IF([Days Overdue]>0 and [Days Overdue]<=30,Receivable,0)

4. 0-60 - =IF([Days Overdue]>30 and [Days Overdue]<=60,Receivable,0)

5. 0-90 - =IF([Days Overdue]>60 and [Days Overdue]<=90,Receivable,0)

6. 90+ - =IF([Days Overdue]>90,Receivable,0)

Hope it helps.

[aveeeeeee7en]

Try Something like this:

if((Num(Today())-Num(Entry Date))<30,'< 30 Days',

if((Num(Today())-Num(Entry Date))>=30 AND (Num(Today())-Num(Entry Date))<60,'30-60 Days',

if((Num(Today())-Num(Entry Date))>=60 AND (Num(Today())-Num(Entry Date))<90,'60-90 Days',

if((Num(Today())-Num(Entry Date))>=90,'>=90 Days'))))  AS InvoiceAgeingBuckets

Regards

Aviral Nag

[martynlloyd]

// First load a table of invoices

Invoices:

LOAD

InvoiceID,

DueDate,

IF(OutstandingAmount=0, 0, Interval(CurrentDate- DueDate, 'D') as DebtorDays

From...

// Then age:

Left join (Invoices)

LOAD

IF(Floor(DebtorDays/30)>3, '>90',  Text(Floor(DebtorDays/30) * 30) as AgeBucket

Resident Invoices;