topic Re: How to find max value and Minvalue in QlikView

How to find max value and Minvalue

kunkumnaveen — Mon, 10 Jun 2019 11:37:54 GMT

Hello All,

I need to find Max Year value and Min year value for Each combination of Product,Model,Mission,Type...

For example in below sample data set

Product	Model	Mission	Type	Year	Values
DRA	4WD AT	AT	Sel	2011	1923
DRA	4WD AT	AT	Sel	2012	1923
DRA	4WD AT	AT	Sel	2013	1923
DRA	4WD AT	AT	Sel	2014	1932
DRA	4WD AT	AT	Sel	2015	1950
DRA	4WD AT	AT	Sel	2016	1950
DRA	4WD AT	AT	Sel	2017	1950
UTI	FASS	MT	line	2012	705
UTI	FASS	MT	line	2013	705
UTI	FASS	MT	line	2014	725
UTI	FASS	MT	line	2015	725
UTI	FASS	MT	line	2016	725
UTI	FASS	MT	line	2017	725
UTI	FASS	MT	line	2018	725
UTI	FASS	MT	line	2019	727

Measure i wrote to get max and Min

1 )Max : Only({<Year={$(=max(Year)}>}Values)

2)Min : Only({<Year={$(=min(Year)}>}Values)

I am getting error

Output:

Max value for 1st row is 1950 (Max year =2017)

Max value for 2nd row is 727 (Max year =2019)

PFA..

Thanks

Naveen

Re: How to find max value and Minvalue

sunny_talwar — Mon, 10 Jun 2019 11:39:48 GMT

Try this instead

For Max

FirstSortedValue(Values, -Year)

For Min

FirstSortedValue(Values, Year)

Re: How to find max value and Minvalue

kunkumnaveen — Mon, 10 Jun 2019 11:50:09 GMT

Thanks Sunny ,
What i am trying to achieve is a CAGR value and the formula is below ...
Pow(
(
only({<Year={$(=max(Year))}>}Values)

/
only({<Year={$(=Min(Year))}>}Values)
),
1/(Max(Year)-Min(Year))-1
)

so how to fit firstsortvalue in the place of max Year and Min Year

Re: How to find max value and Minvalue

sunny_talwar — Mon, 10 Jun 2019 11:52:24 GMT

May be this

Pow( (FirstSortedValue(Values, -Year)/FirstSortedValue(Values, Year)), 1/(Max(Year)-Min(Year))-1 )

Re: How to find max value and Minvalue

sunny_talwar — Mon, 10 Jun 2019 11:55:46 GMT

Check this out

Re: How to find max value and Minvalue

kunkumnaveen — Mon, 10 Jun 2019 12:10:21 GMT

Thanks Sunny , when ever there is a show stopper , u always bring me out of that , i am keep learning from you ...
Once again Thanks Man

Re: How to find max value and Minvalue

kunkumnaveen — Mon, 10 Jun 2019 12:25:48 GMT

Need a suggestion how to add default selection value in firstsortvalue

I mean My default it should take

Feature = CC

as Shown below

Before Feature column Selection

After feature Column selection

I tried this

Pow( (FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)), 1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1 )

Re: How to find max value and Minvalue

kunkumnaveen — Mon, 10 Jun 2019 12:26:10 GMT

Need a suggestion how to add default selection value in firstsortvalue

I mean My default it should take

Feature = CC

as Shown below

Before Feature column Selection

After feature Column selection

I tried this

Pow( (FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)), 1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1 )

Re: How to find max value and Minvalue

sunny_talwar — Mon, 10 Jun 2019 12:29:21 GMT

May be this

If(GetSelectedCount(Feature) = 0, Pow( (FirstSortedValue({<Feature={'CC'}>}Values, -Year)/FirstSortedValue({<Feature={'CC'}>}Values, Year)), 1/(Max({<Feature={'CC'}>}Year)-Min({<Feature={'CC'}>}Year))-1 ), Pow( (FirstSortedValue(Values, -Year)/FirstSortedValue(Values, Year)), 1/(Max(Year)-Min(Year))-1 ) )