Average Maximum in Expression Calculation

[#1]

(I had posted a similar question concerning minimum dates, but had already marked it as correct and didn't know what the etiquette here entailed, so I just posted a modified version.)

I have two date fields, "update_date" and "add_date". There are multiple update dates for each corresponding customer, but only one add date, as shown:

add_date
11/7/2011 9:56:21 PM

update_date
11/15/2011 6:12:16 PM
11/15/2011 4:46:01 PM
11/15/2011 3:19:17 PM
11/15/2011 1:07:36 PM

What I need to do in a chart (via an expression named "Approval") is calculate the average difference between the latest update date and the submit date. What I have now (thanks to a helpful soul) is:

    = avg(aggr(interval(update_date-add_date,'DD'),update_date))

which gives me the average minimum date. How would I calculate the average of all maximum (latest, most recent) dates?

[swuehl · Accepted solution]

How are add_date and update_date related? Do your tables look like this (linked by customer):

LOAD * INLINE [

Customer, add_date

A,    11/7/2011 9:56:21 PM

B,    11/8/2011 9:56:21 PM

];

LOAD * INLINE [

Customer, update_date

A,11/15/2011 6:12:16 PM

A,11/15/2011 4:46:01 PM

A,11/15/2011 3:19:17 PM

A,11/15/2011 1:07:36 PM

B,11/15/2011 6:12:16 PM

B,11/15/2011 1:07:36 PM

];

What is your dimension in the chart? I've tried with Customer, but the expression you posted above does not work. I also do not understand why it calculates the average min interval. Surely I don't really understand your setting.

It tried with above tables and created a chart with dimension Customer and then used:

= avg(aggr(interval(Min(update_date)-only(add_date),'DD'),Customer))

resp.

= avg(aggr(interval(Max(update_date)-only(add_date),'DD'),Customer))

which seem to calculate what I would assume as average min and max intervals (average over Customer in the chart total).

See also attached.

Regads,

Stefan

edit: corrected aggr dimension list

[#3]

The main problem was the correct syntax for the expressions. I'll try using your examples above today and see what's what.

[#4]

That's the syntax I needed, thanks a bunch.