5 Replies Latest reply: Jul 22, 2014 7:38 AM by Martyn Lloyd RSS

    Aging of invoices

      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

        • Re: Aging of invoices

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

          • Re: Aging of invoices
            vikas mahajan

            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

            • Re: Aging of invoices

              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.

              • Re: Aging of invoices
                AVIRAL NAG

                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

                • Re: Aging of invoices
                  Martyn Lloyd

                  // 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;