Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
kunkumnaveen
Valued Contributor

How to find max value and Minvalue

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 

ProductModelMissionTypeYearValues
DRA4WD ATATSel20111923
DRA4WD ATATSel20121923
DRA4WD ATATSel20131923
DRA4WD ATATSel20141932
DRA4WD ATATSel20151950
DRA4WD ATATSel20161950
DRA4WD ATATSel20171950
UTIFASSMTline2012705
UTIFASSMTline2013705
UTIFASSMTline2014725
UTIFASSMTline2015725
UTIFASSMTline2016725
UTIFASSMTline2017725
UTIFASSMTline2018725
UTIFASSMTline2019727

 

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 

Capture.PNG

 

Output:

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

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

 

PFA..

 

Thanks

Naveen

1 Solution

Accepted Solutions

Re: How to find max value and Minvalue

May be this

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

Re: How to find max value and Minvalue

Try this instead

For Max

FirstSortedValue(Values, -Year)

For Min

FirstSortedValue(Values, Year)
kunkumnaveen
Valued Contributor

Re: How to find max value and Minvalue

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

May be this

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

Re: How to find max value and Minvalue

Check this out

image.png

kunkumnaveen
Valued Contributor

Re: How to find max value and Minvalue

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
kunkumnaveen
Valued Contributor

Re: How to find max value and Minvalue

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 

Capture.PNG

 

After feature Column selection Capture3.PNG

Capture2.PNG

I tried this

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

Re: How to find max value and Minvalue

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 

Capture.PNG

 

After feature Column selection Capture3.PNG

Capture2.PNG

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

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