Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
So I want to calculate the estimated payment date for our customers based on the number of days that that specific customer is overdue.
Step1: Calculate the number of days the customer is normally early/late compared to the due date of the invoice. I am using a simpel Avg() aggregated on customer:
Aggr(Avg([Payment Date]-[Due Date])), Customer)
Now my wish is to use this number to add onto the respective customers due dates on their invoices to get the real expected payment date, like this:
Customer | Invoice# | Amount | Due date | Avg days overdue (calculated) | Expected payment date |
---|---|---|---|---|---|
Retailer 1 | 10001 | 3500 | 2013-09-24 | 12 | 2013-10-06 |
Retailer 1 | 10002 | 2500 | 2013-09-24 | 12 | 2013-10-06 |
Retailer 1 | 10003 | 1800 | 2013-09-26 | 12 | 2013-10-08 |
Retailer 1 | 10004 | 1900 | 2013-09-28 | 12 | 2013-10-10 |
Retailer 2 | 10005 | 6000 | 2013-09-25 | 4 | 2013-09-29 |
Retailer 2 | 10006 | 4500 | 2013-09-26 | 4 | 2013-09-30 |
However purely inserting the the above avg(aggr()) calculation only renders one value per customer (which is logical, but annoying due to my wish) like this (also, the calculated average seems to populate the cells in a somewhat random behaviour):
Customer | Invoice# | Amount | Due date | Avg days overdue (calculated) | Expected payment date |
---|---|---|---|---|---|
Retailer 1 | 10001 | 3500 | 2013-09-24 | ||
Retailer 1 | 10002 | 2500 | 2013-09-24 | 12 | 2013-10-06 |
Retailer 1 | 10003 | 1800 | 2013-09-26 | ||
Retailer 1 | 10004 | 1900 | 2013-09-28 | ||
Retailer 2 | 10005 | 6000 | 2013-09-25 | 4 | 2013-09-29 |
Retailer 2 | 10006 | 4500 | 2013-09-26 |
So my question is: How do I go about to make the average days overdue calculation populate all the rows, so that I can calculate an expected payment date on all rows?
Thank you,
David
Hiya,
try Aggr(Avg(Total [Payment Date]-[Due Date])), Customer), Aggr(Nodistinct Avg([Payment Date]-[Due Date])), Customer) or combination of both.
The other option would be to calculate all in the load script - I would do it this way.
Hope that helps.
Any ideas?
Hiya,
try Aggr(Avg(Total [Payment Date]-[Due Date])), Customer), Aggr(Nodistinct Avg([Payment Date]-[Due Date])), Customer) or combination of both.
The other option would be to calculate all in the load script - I would do it this way.
Hope that helps.
I wouldn't have a clue how to do this in a table.
You should be able to do this in the script. Something similar to:
TEMP_CUST:
Load
text(AccountNo) as TempSL_CustCode
Resident CUSTOMER;
// Associate the average number of days to pay for each customer against each line
Left Join(TEMP_CUST)
Load
Only(text([Cust Code])) as TempSL_CustCode,
Sum(Date([Paid Date]) - Date([Inv Date]))/Count(Date([Inv Date])) as TempAvgDaysToPay
FROM $(ServerNamePath)\invoices Group By [Cust Code];
// Associate the result with the SLEDGER, putting zero in as Avg Days if the Cust has no Sales History to go by
Inner Join(SLEDGER)
Load
text(TempSL_CustCode) as SL_CustCode,
if(isnull(TempAvgDaysToPay), 0, TempAvgDaysToPay) as AvgDaysToPay
Resident TEMP_CUST;
Drop table TEMP_CUST;
Left Join(SLEDGER)
LOAD
text(TRANSACTION_NUMBER) as TRANSACTION_NUMBER,
SLedgerLineNo,
Date(SL_INVOICEDATE+AvgDaysToPay) as EXPECTED_PAID_DATE,
Resident SLEDGER;
Regards
Andy
Thank you Yavor!
The latter example helped me!
Thanks Andrew!