Aggregate average to populate all cells in straight table

[#1]

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

[#2]

Any ideas?

[yavoro · Accepted solution]

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.

[#4]

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

[#5]

Thank you Yavor!

The latter example helped me!

[#6]

Thanks Andrew!