Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Average Maximum in Expression Calculation

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Average Maximum in Expression Calculation

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 Replies
MVP
MVP

Re: Average Maximum in Expression Calculation

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

Not applicable

Average Maximum in Expression Calculation

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

Not applicable

Average Maximum in Expression Calculation

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