topic Re: Average Maximum in Expression Calculation in QlikView

Average Maximum in Expression Calculation

Tue, 14 Feb 2012 22:12:58 GMT

(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?

Re: Average Maximum in Expression Calculation

swuehl — Tue, 14 Feb 2012 23:34:52 GMT

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).

Average Maximum in Expression Calculation

Wed, 15 Feb 2012 14:40:25 GMT

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

Average Maximum in Expression Calculation

Wed, 15 Feb 2012 20:39:03 GMT

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