Average Minimum in Expression Calculation

[#1]

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 earliest update date and the submit date. What I have now is:

avg((update_date-add_date)*24)

This doesn't give me what I need, though. What I need is (theoretically) the following:

avg((Min(update_date)-add_date)*24)

While that checks out in Qlikview, that makes the Approve expression disappear from both the chart and the legend altogether. I tried using "Max" instead of  "Min" in the expression in the hopes that it would reappear at all  but no luck. I'm hoping this is just a syntax error; any help is appreciated.

[#2]

i think you might need to use the AGGR function to first get all the update_dates before then getting the min value

try

avg((Min(aggr(update_date))-add_date)*24)

not tried it myself but hope it helps

Lewis

[CELAMBARASAN · Accepted solution]

Hi,

     You mean this type of solution?

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

Celambarasan