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!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

17 Replies
vinieme12
Champion III
Champion III

App Attached

Script:

FACT:

LOAD Client,

     [Sales Document],

     [Sales Amount],

     [Due Date]

FROM

(ooxml, embedded labels, table is Hoja1);

left join

LOAD

     Payments,

     [Sales Document1] as [Sales Document],

     [Payed Amount],

     [Payment Date]

   

FROM

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

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

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

MK_QSL
MVP
MVP

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

vinieme12
Champion III
Champion III

You can use the expression by @Manish below.

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

Here is the app and database mrkachhiaimpvinieme12

thank you !!

Not applicable
Author

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)

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MK_QSL
MVP
MVP

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

Not applicable
Author

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?