Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Apart from QV script, practically I have never seen excluding off days for ageing.
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
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.
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
// 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;