topic Re: Average without a Max and min value in Aggregation in QlikView

Average without a Max and min value in Aggregation

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

Hi all

I have this:

Producto	PRECIO	TIENDA
1	10	1
1	31	1
1	4	1
1	2	1
2	4	1
2	7	1
2	5	1
2	10	1
1	5	2
1	10	2
1	15	2
2	10	2
2	20	2
2	20	2
2	30	2

I would like to have the average for each "TIENDA" and "PRODUCTO" without the max and the min value for each agregation.

TIENDA 1: PRODUCTO 1 AVERAGE 17.5

TIENDA 1: PRODUCTO 2 AVERAGE 6

TIENDA 2: PRODUCTO 1 AVERAGE 10

TIENDA 2: PRODUCTO 2 AVERAGE 20

I'm going to use in a expression of a simple table chart using TIENDA as dimension

I need Helpppppppppppp .... I'll pay you a beer ..... or two

Regards

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Wed, 17 May 2017 17:10:11 GMT

First one Average is 11.5 ??? or 17.5 ???

May be this?

Create Straight Table and dimensions are Tienda, Producto

Expression simple Avg(PRECIO)

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Wed, 17 May 2017 17:10:50 GMT

I need Helpppppppppppp .... I'll pay you a beer ..... or two

Hahaha, Don't offer only for Beer. Why not deposit amount to my bank ??

Re: Average without a Max and min value in Aggregation

shiveshsingh — Wed, 17 May 2017 17:16:47 GMT

Take Avg function as expression.

tienda and producto as dimension.

Re: Average without a Max and min value in Aggregation

Anonymous — Wed, 17 May 2017 17:30:35 GMT

HI all

Maybe I explained bad...

I would like to have the average of PRECIO in every cross of TIENDA and PRODUCTO excluding the maxim and minimun value for each cross.

The result I post was what I want .... then mean for TIENDA 1: PRODUCTO 1 AVERAGE 17.5 is because for this cross I don't use 10 (is the max) and 2 (is the minimum)

Regards

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Wed, 17 May 2017 18:26:50 GMT

Got it, But here again i doubt whether how you say 10 is Maximum. seems 31 is Max for you? and 2 is minimum. You need to exclude these but output should come 7 only. Please help me to figure out this?

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Wed, 17 May 2017 18:29:14 GMT

check this? It may helps you

Sum(Aggr(Avg({<PRECIO -= {'$(=Max(PRECIO))', '$(=Min(PRECIO))'}>} PRECIO), Producto, TIENDA))

Re: Average without a Max and min value in Aggregation

Anonymous — Thu, 18 May 2017 07:10:39 GMT

HI Anil

Thanks for all

I'll give you more information ... in our chart the unique dimension is "TIENDA", not "PRODUCTO", we want that the result be the sum of the average of "PRECIOS" for every "PRODUCTO"excluding MAx an MIn of each "PRODUCTO".

The result should be:

average excluding (MIN Max) PRODUCTO1 TIENDA 1 = 17.5

average excluding (MIN Max) PRODUCTO2 TIENDA 1 = 6

SUM=23.5

average excluding (MIN Max) PRODUCTO1 TIENDA 2 = 10

average excluding (MIN Max) PRODUCTO2 TIENDA 2 = 20

SUM=30

TIENDA	RESULT
1	23.5
2	30

thank you very much

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Thu, 18 May 2017 15:42:46 GMT

Finally point from my end - How 17.5 Comes into the picture?

Re: Average without a Max and min value in Aggregation

Anonymous — Thu, 18 May 2017 15:48:13 GMT

Sorry .... my fault ... i GIVE YOU BAD THE AVERAGE ... in resume

we have 4 PRECIOS for PRODUCTO 1,

10, 31, 4 y 2

The mean excluding the max and in value is (10+4)/2 = 7

SORRYYYYYY

REGARDS

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Thu, 18 May 2017 16:33:37 GMT

Can you look this?

Sum(Aggr(Avg({<PRECIO -= {'$(=Max(PRECIO))', '$(=Min(PRECIO))'}>} PRECIO), Producto, TIENDA))

Re: Average without a Max and min value in Aggregation

Anonymous — Thu, 18 May 2017 16:38:49 GMT

Hi Anil

Thanks for answer

We tried do it but it quits the max of all the PRECIO and the min of alL the PRECIO, don't subset the data in PRODUCTO ... I don't know if I'm explaining very good so .. later I'll send u a small QV to see what happens ... ok?

Re: Average without a Max and min value in Aggregation

Anonymous — Fri, 19 May 2017 07:46:40 GMT

Do you know how to attach documents??

It's looks imposible

Regards

Re: Average without a Max and min value in Aggregation

Anonymous — Fri, 19 May 2017 08:16:26 GMT

Hi Anil

Thaks a lot in advance

Here the real situation, I give u a small example in Qv and a small Excel to make the checking

Thank you very very very much ....

Re: Average without a Max and min value in Aggregation

Anonymous — Tue, 23 May 2017 06:31:57 GMT

HI Anil .. can u help me with this problem:::

Regards

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Tue, 23 May 2017 07:23:24 GMT

Still i am confusing, Why 17.5 Comes up instead of 7? Can you explain me the reason how you get for this filter

Re: Average without a Max and min value in Aggregation

Anonymous — Tue, 23 May 2017 08:44:39 GMT

HI Anil, thank you for answering, I have a mistake with the average, the correct value is 7 .... sorry

Thanks a lot, I put attached an small example to work with

Re: Average without a Max and min value in Aggregation

Anil_Babu_Samineni — Tue, 23 May 2017 12:46:27 GMT

In excel you are showing 7872, But in text object of Qlikview you want it as 8290. Can i know the Difference

For 7872, You need to write this?

Sum( Aggr(Avg(LM_PRECIO), LM_PRODUCTO_ID, LM_ENSENA_TEXT))

Or Remove the LM_PRODUCTO_ID from Table and then replace to expression