17 Replies Latest reply: Jan 5, 2017 2:03 PM by Ignacio Garribia RSS

    Payment delay by client

    Ignacio Garribia

      Hello Everyone! I need to calculate the avg payment delay by client.

       

      I've created a simplified example in excel with the formula.

       

      In the example, there are 3 receipts with different dates that are related to one sales document each. Two sales documents are already payed. The other sales document has partial payments. The aging for this sales document should consider dates and payments amount , and today's date for the debt. ( See the yellow cell in excel )

       

      The green cell in the example contains the result that i`m looking for.

       

      I'd appreciate very much for your help!

        • Re: Payment delay by client
          Vineeth Pujari

          App Attached

           

           

          Script:

           

          FACT:

          LOAD Client,

               [Sales Document],

               [Sales Amount],

               [Due Date]

          FROM

          [C:\Users\vp51284\Downloads\Paymente delay by client.xlsx]

          (ooxml, embedded labels, table is Hoja1);

           

          left join

          LOAD

               Payments,

               [Sales Document1] as [Sales Document],

               [Payed Amount],

               [Payment Date]

             

          FROM

          [C:\Users\vp51284\Downloads\Paymente delay by client.xlsx]

          (ooxml, embedded labels, table is Hoja1);

           

           

           

          244724.PNG

           

           

          Expression:

          =if(isnull([Payment Date]-[Due Date]),

          (sum(TOTAL <Client,[Sales Document],[Payment Date],Payments> aggr(

          (([Payment Date]-[Due Date])*[Payed Amount])

          ,Client,[Sales Document],[Payment Date],Payments))

          +

          sum(TOTAL <Client,[Sales Document]> aggr(

          [Sales Amount] - sum([Payed Amount])

          ,Client,[Sales Document]))*(today()-[Due Date]))

          /[Sales Amount]

          ,

          [Payment Date]-[Due Date])

          • Re: Payment delay by client
            Ignacio Garribia

            Hello Vineeth Pujari! Thank you very much!!

             

            The calculation is working  for each document when sales document field is displayed on the table.

             

            As you can see in the attached image, the avg is 25,33, but it should be 31,85 ( ponderated avg)

             

            When the sales document field is not displayed on the table, the calculation is not working, which makes impossible to rank clients by delay avg

             

            Do you know how to do it?

             

            Ranking by client.png

            • Re: Payment delay by client
              Manish Kachhia

              Create a Straight Table

              Dimensions

              Client

              Sales Document

              Sales Amount

              Due Date

               

              Expressions

              For Delay use below expression

              =SUM(Aggr(Sum(Aggr([Payed Amount] * ([Payment Date] - [Due Date]),Client,Payments))+(Today() - [Due Date]) * (Only([Sales Amount])-SUM([Payed Amount])),Client,[Sales Document]))

              /

              SUM(Aggr(Only([Sales Amount]),Client,[Sales Document]))

                • Re: Payment delay by client
                  Ignacio Garribia

                  Hello Manish! thank you very much, we are almost done. The expression works in the case I've sent you (client AAA), but when I add another client and the rows related, I doesn't work.

                  I attached the .qvf file and  the excel database. The result for client BBB should be 42.33 days ( all the calculation is in the excel file)

                • Re: Payment delay by client
                  Ignacio Garribia

                  Here is the app and database mrkachhiaimp vinieme12

                  thank you !!

                    • Re: Payment delay by client
                      Vineeth Pujari

                      If you only have Client as Dimension , then use

                       

                      =SUM(TOTAL <Client> Aggr(Sum(Aggr([Payed Amount] * ([Payment Date] - [Due Date]),Client,Payments))+(Today() - [Due Date]) * (Only([Sales Amount])-SUM([Payed Amount])),Client,[Sales Document]))

                      /

                      SUM(TOTAL <Client> Aggr([Sales Amount],Client,[Sales Document]))

                       

                       

                      if you want to have 2 dimensions, ie by Client ,and by Sales Document use

                      =SUM(Aggr(Sum(Aggr([Payed Amount] * ([Payment Date] - [Due Date]),Client,Payments))+(Today() - [Due Date]) * (Only([Sales Amount])-SUM([Payed Amount])),Client,[Sales Document]))

                      /

                      SUM(Aggr(Only([Sales Amount]),Client,[Sales Document]))

                    • Re: Payment delay by client
                      Manish Kachhia

                      Try this..

                       

                      SUM(Aggr(Sum(Aggr([Payed Amount] * ([Payment Date] - [Due Date]),Client,Payments,[Sales Document]))+(Today() - [Due Date]) * (Only([Sales Amount])-SUM([Payed Amount])),Client,[Sales Document]))

                      /

                      SUM(Aggr(Only([Sales Amount]),Client,[Sales Document]))

                       

                      This will give you slightly different result because the calculations in Excel file is assuming Today() as 20/12/2016 but this expression will calculated Today() as 30/12/2016...

                      You can create a variable..

                       

                      Let vToday = Num(Date('29/12/2016'));

                      and use it in expression ...

                      Just replace vToday inplace of Today()