Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Aggregation problem in set analysis

Hi all,

I got a problem in a set analysis.

I run the following :

avg({$<date={"$(=min(date))"}>} value) 

Which should give me the average of the value on the minimum date.

However the dollar sign function $(=min(date)) return the minimum date of the whole selected data set.

So if I produce the following table :

qlik example.png

You can see that most of time Max average and Min average are null, simply because on the $(=min(date)) is using the minimum and the maximum of the whole selected data.


I have been trying couple of options (use column(), some aggregation ...)

Do you have an idea so the max average and min average will return the average on the Max date and min date for that code ?

Thanks you !

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregation problem in set analysis

Try this:

Max

FirstedSortedValue(Aggr(Avg(value), code, date), -Aggr(date, code, date))


Min

FirstedSortedValue(Aggr(Avg(value), code, date), Aggr(date, code, date))

3 Replies
MVP
MVP

Re: Aggregation problem in set analysis

Try this:

Max

FirstedSortedValue(Aggr(Avg(value), code, date), -Aggr(date, code, date))


Min

FirstedSortedValue(Aggr(Avg(value), code, date), Aggr(date, code, date))

MVP
MVP

Re: Aggregation problem in set analysis

As alternative, you can try something like

=Avg( Aggr(If( date = Max(Total<code> date), value), code, date, value))

=Avg( Aggr(If( date = Min(Total<code> date), value), code, date, value))

Not applicable

Re: Aggregation problem in set analysis

I works pretty well !

I need to test few case but you apparently cracked it ! Thanks