topic Re: Aggregation problem in set analysis in QlikView

Aggregation problem in set analysis

Fri, 19 Aug 2016 02:39:28 GMT

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 :

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 !

Re: Aggregation problem in set analysis

sunny_talwar — Fri, 19 Aug 2016 08:44:58 GMT

Try this:

Max

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

Min

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

Re: Aggregation problem in set analysis

swuehl — Fri, 19 Aug 2016 22:17:20 GMT

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

Re: Aggregation problem in set analysis

Mon, 22 Aug 2016 02:37:35 GMT

I works pretty well !

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