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

# Payment delay by client

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

App Attached

Script:

FACT:

[Sales Document],

[Sales Amount],

[Due Date]

FROM

(ooxml, embedded labels, table is Hoja1);

left join

Payments,

[Sales Document1] as [Sales Document],

[Payed Amount],

[Payment Date]

FROM

(ooxml, embedded labels, table is Hoja1);

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

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?

• ###### Re: Payment delay by client

You can use the expression by @Manish below.

• ###### Re: Payment delay by client

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

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

Here is the app and database mrkachhiaimp vinieme12

thank you !!

• ###### Re: Payment delay by client

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

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()

• ###### Re: Payment delay by client

Thank you Manish, it works great. What if I want to select only sales documents which are not payed yet ir order to calculate the aging of unpaid sales document. How would you filter them on the expression?

• ###### Re: Payment delay by client

Provide me an example from your sample data..

Select something and explain me what you want..

• ###### Re: Payment delay by client

Hello Manish. In the excel file "Validation" I'd send you,  the sum of sales is \$1063517, while the sum of payments is \$727881. I want to know the aging of the debt (sales- payments)( \$335.635). The aging must be the same expression you made but only considering sales documents where (sales-payments>0)

Please tell me if I am clear enough. Tks again!

• ###### Re: Payment delay by client

What is \$335.635 here?

• ###### Re: Payment delay by client

Hello! Sum (Sales Amount) - Sum( Payed Amount)= 335.635

In the example, the expression result must be 33,25 days.  As you can see in the example, the avg is ponderated by sales amount - payed amount .

• ###### Re: Payment delay by client

Try this..

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

/

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

*

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

,Client,[Sales Document])))

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

• ###### Re: Payment delay by client

excellent! it works great!!!  thank you very much Manish, it's awesome to have your support !

• ###### Re: Payment delay by client

This?

SUM({<[Sales Document] = {"=sum([Sales Amount]) > sum([Payed Amount])"} >}

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]))

• ###### Re: Payment delay by client

Hello Vineeth, thank you! I tryed this, and works well, but know the avg must be ponderated by sales amount- payed amount. I tryed many things but could'nt get to the result. Please take a look to the answer to Manish where I put an example

SUM({<[Sales Document] = {"=sum([Sales Amount]) > sum([Payed Amount])"} >}

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({<[Sales Document] = {"=sum([Sales Amount]) > sum([Payed Amount])"} >}Aggr(Only([Sales Amount]),Client,[Sales Document]))