Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
simonhelie
Contributor III
Contributor III

Get the date of min and max value

Hi everyone,

Another question for you.

In my pivot table I'd like to display the date of my min and max values.

I tried the following:

(Min)=FirstSortedValue([Date],[Resultat])

(Max)=FirstSortedValue([Date],-[Resultat])

But I'm not getting exactly what I want... here's an exampleminandmax.png

So as you can see and I have a min and max value for each row. But some dates are unexpectedly missing and I don't understand why.

Would someone have a hint?

Thanks,

1 Solution

Accepted Solutions
rwunderlich

You can try:

(Min)=FirstSortedValue(DISTINCT [Date],[Resultat])


to see if it gives the right result. Or explicitly add a bias to get the min date when there is a tie.


(Min)=FirstSortedValue([Date],[Resultat] + (Date * 0.0001))


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

View solution in original post

5 Replies
ogautier62
Specialist II
Specialist II

Hi

you may have tie values,

in that case firstsortedvalues return null

regards

agigliotti
Partner
Partner

maybe this:


=FirstSortedValue( Date, Aggr( Sum(Resultat), Date ) )


I hope it helps.

simonhelie
Contributor III
Contributor III
Author

You are right.

I have multiple times the same min and the same max.

So what if I want the minimum date among my minimum values?

Thanks!

rwunderlich

You can try:

(Min)=FirstSortedValue(DISTINCT [Date],[Resultat])


to see if it gives the right result. Or explicitly add a bias to get the min date when there is a tie.


(Min)=FirstSortedValue([Date],[Resultat] + (Date * 0.0001))


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

simonhelie
Contributor III
Contributor III
Author

That worked:

=FirstSortedValue([Date],[Resultat] + (Date * 0.0001))


Thank you!