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

[ahaahaaha]

Hi Eugeniya,

May be like this

total Litres:    Aggr(Sum(Num(Litres)), MonthYear, Type, Country)

total Amount:   Aggr(Sum(Num(Amount)), MonthYear, Type, Country)

Price: Aggr(Sum(Num(Amount)), MonthYear, Type, Country)/Aggr(Sum(Num(Litres)), MonthYear, Type, Country)

Regards,

Andrey

[sunny_talwar]

So you need this in a text box object to count the min and max Amount? So you need a text box saying 2 for min and another one for max saying count of 1?

[eugeniyaromanov]

Hello!

Thanks for the attention!

Unfortunately, it's not what I wanted.

I need to count min and max price when I make a selection.

I need a text object with Min price (for a selection(  MonthYear, Type, Country))

and a text object with Max price.

And because I'm not very good in explanation, I colored it pink and red.

But anyway, I've tried it, didn't work

[sunny_talwar · Accepted solution]

May be this

Min

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

Max

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

[eugeniyaromanov]

Hello!

Yes, almost.

Min and max price.

I need one box per each.

Min should tell 0.22 (even it there are two or more).

Same for max, it should tell 48.21.

Dimension to change always MonthYear, Type and Country.

[sunny_talwar]

Try these

Min

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

Max

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

[eugeniyaromanov]

Wow, perfect!

Thanks!

How do you do that? Every time you help, it always the right answer!

How long have you been working with Qlik?

[sunny_talwar]

I am glad it worked... I have been working for 2 and half years now

[eugeniyaromanov]

Btw,

if I want another text box, where I want to put a name of Country with min and max price what to do?

If i put if statement:

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

it wouldn't work..