topic Min and Max values in QlikView

Min and Max values

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

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!

Eugeniya

Re: Min and Max values

ahaahaaha — Mon, 03 Apr 2017 08:56:59 GMT

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

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 08:57:03 GMT

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?

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 09:06:00 GMT

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

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 09:08:58 GMT

May be this

Min

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

Max

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

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 09:09:25 GMT

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.

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 09:10:29 GMT

Try these

Min

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

Max

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

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 09:13:07 GMT

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?

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 09:15:33 GMT

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

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 09:19:16 GMT

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..

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 09:22:30 GMT

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

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

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 09:49:12 GMT

1st

works as Max

2nd

don't work

3d and 4th work as Max

Re: Min and Max values

sunny_talwar — Mon, 03 Apr 2017 10:50:14 GMT

3d and 4th work as Max

3rd and 4th both works as max?

Re: Min and Max values

eugeniyaromanov — Mon, 03 Apr 2017 11:12:22 GMT

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

Thanks!

3d and 4th work perfect.