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

[joeallen]

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?

[rajivmeher]

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.

[raman_rastogi]

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