Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sgonzalez_adaso
Partner - Contributor III
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:

 

YEARCUSTOMERSALES
2014A50
2014B80
2014A100
2014B30
2015A150
2015B60
2015A30
2015B100
2016A80
2016B70
2016A50
2016B40

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
Anonymous
Not applicable

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

View solution in original post

5 Replies
MK_QSL
MVP
MVP

Dimension

CUSTOMER

Expression

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

sgonzalez_adaso
Partner - Contributor III
Partner - Contributor III
Author

My mistake.

My dimension is CUSTOMER, of course.

This expression is not working for me.

vishsaggi
Champion III
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')

Anonymous
Not applicable

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

MK_QSL
MVP
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?