Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

17 Replies
MK_QSL
MVP
MVP

Provide me an example from your sample data..

Select something and explain me what you want..

I will try to help you..

Not applicable
Author

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!

MK_QSL
MVP
MVP

What is $335.635 here?

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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 .

Debt aging.png

Not applicable
Author

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

MK_QSL
MVP
MVP

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

Not applicable
Author

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