Min and Max values

[eugeniyaromanov]

Hello!

I've got another question.

I need to count in a text field min and max for a price.

So basically I need to show 0.22 (pink number) as min and 48.21 (red number) as max.

But when I try to count it it always show something else.

All this numbers are not in original data, they all are calculated.

I realize than I need to count it depending on fields: MonthYear, Type and Country, but don't know how.

Here is my table:

MonthYear	Type	Country	total Litres	total Amount	Price
			=sum(Litres)	= sum(Amount)	=sum(Amount)/sum(Litres)
oct-2016	type1	FR	1'901'848.00	425'740.89	0.22
dec-2016	type1	FR	1'828'807.00	417'415.15	0.23
nov-2016	type1	FR	1'687'858.00	374'598.90	0.22
oct-2016	type2	BE	882'472.38	673'299.21	0.76
dec-2016	type2	BE	798'774.70	634'574.94	0.79
nov-2016	type2	BE	744'572.30	558'997.41	0.75
dec-2016	type2	ES	636'515.96	488'581.45	0.77
nov-2016	type2	ES	556'924.56	400'173.86	0.72
oct-2016	type2	ES	421'531.36	311'164.87	0.74
oct-2016	type2	FR	321'233.95	278'641.32	0.87
dec-2016	type2	FR	249'704.06	226'250.38	0.91
nov-2016	type2	FR	206'497.34	176'489.08	0.85
dec-2016	type1	ES	2'322.25	42'316.93	18.22
dec-2016	type1	BE	1'836.02	42'930.21	23.38
nov-2016	type1	ES	1'801.00	37'118.23	20.61
oct-2016	type1	ES	1'393.00	24'903.15	17.88
oct-2016	type1	BE	983.00	47'394.90	48.21
nov-2016	type1	BE	933.00	41'168.73	44.13

Original Data looks like:

MonthYear	Type	Country	Litres	Amount
oct-2016	type2	Belgium	0,05	0,04
oct-2016	type2	Belgium	0,06	0,04
oct-2016	type2	Belgium	0,08	0,06
oct-2016	type2	Belgium	0,09	0,07
oct-2016	type2	Belgium	0,1	0,08
oct-2016	type2	Belgium	0,16	0,13
oct-2016	type2	Belgium	0,29	0,23
oct-2016	type2	Belgium	1,16	0,87
oct-2016	type2	Belgium	1,25	0,97
oct-2016	type2	Belgium	1,7	1,32
oct-2016	type2	Belgium	1,71	1,32
oct-2016	type2	Belgium	1,74	1,35

etc.. there are about 22453 lines in original data.

I'll be glad to answer all questions!

BR

Eugeniya

[sunny_talwar]

May be this

Min

=FirstSortedValue(Country, Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))

Max

=FirstSortedValue(Country, -Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))

or

Min

Concat(DISTINCT Aggr(If(Sum(Amount)/Sum(Litres) = Min(TOTAL Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country)), Country), MonthYear, Type, Country))

Max

Concat(DISTINCT Aggr(If(Sum(Amount)/Sum(Litres) = Max(TOTAL Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country)), Country), MonthYear, Type, Country))

[eugeniyaromanov]

1st

works as Max

2nd

don't work

3d and 4th work as Max

[sunny_talwar]

3d and 4th work as Max

3rd and 4th both works as max?

[eugeniyaromanov]

M... I mush have typed smth wrong first time, now it works well!

Thanks!

3d and 4th work perfect.