topic Help with Top N with avg(aggr(sum....] function in QlikView

Help with Top N with avg(aggr(sum....] function

joeallen — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I currently have a cyclic group with YearMonth, YearWeek, Date as dimension.

Then I have the following simple expression:

=Avg(Aggr(Sum({<OrderType={PurchaseOrder}>} ValueEUR), Date))

Which effectively gives me the daily (Date) average sum of ValueEUR, which works great.

But now I also have the field Country. So I want a graph that shows me which top 5 countries had the biggest daily average of ValueEUR, using the expression above.

However, when I add Country as second dimension under the cyclic time group, I, whilst also enabling "show only largest 5 values" under dimension limits for Country, only get 1 country to show up in the graph ??? I don't understand why it doesn't work. Do I also need to use rank function in the expression or something? If so, how?

Re: Help with Top N with avg(aggr(sum....] function

rajivmeher — Wed, 12 Sep 2018 12:37:21 GMT

Hi joeallen‌

Will it be possible to share sample qvw to understand the issue better?

Seems like its a problem because of the current expression, which aggregates ValueEUR by Date. When you add Country to the dimension, it might not be calculating correctly. You may have to add another expression for Country as:

=Avg(Aggr(Sum({<OrderType={PurchaseOrder}>} ValueEUR), Country))

However it would be easier to identify the issue if we can look at the sample QVW.

Regards

Rajiv.

Re: Help with Top N with avg(aggr(sum....] function

raman_rastogi — Wed, 12 Sep 2018 15:20:28 GMT

Hi Joe,

To find Top 5 countries you can you rank function

May be like this.

=Avg(Aggr(sum({$<OrderType={"=rank(sum({<OrderType={PurchaseOrder}>} ValueEUR))<=5"}>} ValueEUR),Date))

And add Country in your cyclic group.

It would be easier if you can share some sample data along expected output.

Best

Raman