Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

f1234567
New Contributor III

How to calculate an average of three cheapest / most expensive products?

Good Morning,

I have a question of how to calculate the average of  the 3 cheapest products in a list / respectively the three most expensive products in a list products with prices at a certain Date.

Example:

DatProductPrice
1.2.18A10,00
1.2.18B9,78
1.2.18C9,90
1.2.18D7,55
1.2.18E5,54
1.2.18F6,70
1.2.18G3,30
1.2.18H2,90
1.2.18I2,90
1.2.18J9,97
1.2.18K9,97
1.2.18L9,97
1.2.18M2,31
1.2.18N2,31
1.2.18O2,31
1.2.18P0,98
1.2.18Q

0,99

As a result for the "average of the 3 most expensive" I would like to see:  Average = 9,98

   

DatProductPrice
1.2.18A10,00
1.2.18J9,97
1.2.18K9,97

(A+J+K )/3= 9,98

Remarks:

a) Although the Price of product "L" is the same as "J" and "K", it is not taken into account, because I only Need the first three cheapest products.

c) In case  there are only two products in the list, (e.g. at Date "Dat 1.3.18"  only Product A and B) the calculation should be A+B/2.

I have tried it in many ways, but without acceaptable results.

Thanks for your help!

   

8 Replies
vishsaggi
Esteemed Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

may be try like

Correction:

= Avg(Aggr(IF(Aggr(Rank(Sum(Price),4), Product)<=3, Sum(Price)), Product))

If date changes use this in aggr

= Avg(Aggr(IF(Aggr(Rank(Sum(Price),4), Dat, Product)<=3, Sum(Price)), Dat,Product))

vishsaggi
Esteemed Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Another way is using Pivot table:

CheapestPrice:
LOAD *, Date(Date#(Dat, 'M.D.YY')) AS DateFormatted INLINE [
Dat, DatID, Price, Product
1.2.18, 43102, 0.98, P
1.2.18, 43102, 0.99, Q
1.2.18, 43102, 2.31, M
1.2.18, 43102, 2.31, N
1.2.18, 43102, 2.31, O
1.2.18, 43102, 2.90, H
1.2.18, 43102, 2.90, I
1.2.18, 43102, 3.30, G
1.2.18, 43102, 5.54, E
1.2.18, 43102, 6.70, F
1.2.18, 43102, 7.55, D
1.2.18, 43102, 9.78, B
1.3.18, 43102, 9.90, C
1.3.18, 43102, 9.97, J
1.2.18, 43102, 9.97, K
1.2.18, 43102, 9.97, L
1.2.18, 43102, 10.00, A
]
;

using Pivot table add

Dim:

Dat

= Aggr(IF(Aggr(Rank(Sum(Price),4), Dat, Product)<=3, Product), Product)

Price

Expr:

= Avg(TOTAL <Dat> Aggr(IF(Aggr(Rank(Sum(Price),4), Dat, Product)<=3, Sum(Price)/Count(Price)), Dat,Product))

you will see like below:

f1234567
New Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Many thanks! Great!

Do you have a suggestion for the cheapest 3 products at a certain date? Something like "-"rank ?

f1234567
New Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Great! Really good idea. Many thanks!!

Do you have a suggestion for the lowest 3 Prices as well?

vishsaggi
Esteemed Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Yes just use - before the sum like

Dim:

Dat

= Aggr(IF(Aggr(Rank(-Sum(Price),4), Dat, Product)<=3, Product), Product)

Price

Expr:

= Avg(TOTAL <Dat> Aggr(IF(Aggr(Rank(-Sum(Price),4), Dat, Product)<=3, Sum(Price)/Count(Price)), Dat,Product))

passionate
Valued Contributor

Re: How to calculate an average of three cheapest / most expensive products?

Good way to identify lowest 3 avg.

f1234567
New Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Many thanks! Very helpful.

vishsaggi
Esteemed Contributor III

Re: How to calculate an average of three cheapest / most expensive products?

Can you close the thread accordingly if it answered your query.

Community Browser