How to calculate average value

[#1]

Hi,

I have two tables:

Orders:
load * Inline
[
MemberId,EnterDate
1,'2010/01/01'
2,'2010/01/02'
3,'2010/01/03'

];

Transaction:
load * Inline
[
MemberId,ChargeDate,Amount
1,'2010/01/01',100
2,'2010/01/02',200
1,'2010/02/01',300

];

I want to calculate the average Amount by EnterDate, it looks like this:

Month, Total Members, Average Amount

1, 3, 300/3

You could find there is no transaction for memberid 3, but it still need to be calculated.

How to do it? I want to to show the data in the pivot table.

Thanks.

[matt_crowther]

Isaac,

Attached should be a solution. I've created the required values for the calculation in the load script instead of the frontend as the EnterDate and ChargeDate cause a conflict when trying to use one or the other as a base for any table.

Once the data is in via the script then it should be straight forward to use the values as required in a Pivot, chart or table.

Hope that helps,

Matt - Visual Analytics Ltd