topic Re: Average of max values? in QlikView

Average of max values?

sgonzalez_adaso — Mon, 28 Nov 2016 15:49:38 GMT

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?

Re: Average of max values?

MK_QSL — Mon, 28 Nov 2016 15:55:48 GMT

Dimension

CUSTOMER

Expression

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

Re: Average of max values?

sgonzalez_adaso — Mon, 28 Nov 2016 15:59:59 GMT

My mistake.

My dimension is CUSTOMER, of course.

This expression is not working for me.

Re: Average of max values?

vishsaggi — Mon, 28 Nov 2016 16:24:56 GMT

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

Re: Average of max values?

Anonymous — Mon, 28 Nov 2016 16:34:29 GMT

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

Re: Average of max values?

MK_QSL — Mon, 28 Nov 2016 18:29:25 GMT

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?