Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
(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?
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
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
The main problem was the correct syntax for the expressions. I'll try using your examples above today and see what's what.
That's the syntax I needed, thanks a bunch.