# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Average of max values?

Hello everyone,

I explain the case with which I have been fighting for a couple of days.

I need to get the average value of a group of maximum values. The example would look something like:

 YEAR CUSTOMER SALES 2014 A 50 2014 B 80 2014 A 100 2014 B 30 2015 A 150 2015 B 60 2015 A 30 2015 B 100 2016 A 80 2016 B 70 2016 A 50 2016 B 40

In a table I am giving the maximum sales value per customer, in this case, the maximum values, per year and customer would be 100 - 80 for 2014, 150 - 100 for 2015 and 80 - 70 for 2016.

On the other hand, in another table, I need to calculate the average of the maximum sales per customer, this time without grouping per year. Thus, for client A (100 + 150 + 80) / 3 = 110, and for client B (80 + 60 + 70) / 3 = 83.3.

I am trying to get that value by using the AGGR function but I can not find the right grouping.

My test is something like this:

= Aggr (MAX [[Sales]), [Client], [Year]

Can it be done with AGGR? Is there any way to do it?

1 Solution

Accepted Solutions
Creator III

avg(DISTINCT Aggr(max(VENTAS),ANO,CLIENTE))

5 Replies
MVP

Dimension

CUSTOMER

Expression

=Avg(Aggr(Max(SALES),CUSTOMER,YEAR))

Partner - Contributor III
Author

My mistake.

My dimension is CUSTOMER, of course.

This expression is not working for me.

Champion III

Is this something you are looking for ?

FirstTable:

Dim -> YEAR,

CUSTOMER

Expr -> = Max(SALES)

2nd Table:

Dim -> CUSTOMER

Expr-> = Num(Sum(Aggr(MAX(SALES), CUSTOMER,YEAR ))/Count(Aggr(MAX(SALES), YEAR,CUSTOMER )), '#,##0.00')

Creator III

avg(DISTINCT Aggr(max(VENTAS),ANO,CLIENTE))

MVP

When you said it's not working? Can you explain little more here? Which dimensions you have used? What is the expression you have used?

The expression I gave was for 2nd requirement.

For both of your requirements, do you want to use CUSTOMER and YEAR both as Dimensions?

Community Browser