Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tomhovens
Contributor II
Contributor II

Problem with firstsortedvalue

Hey All,

I'm looking for the first date with the max occupancy. The table with the input is;

ItemnrDateOccupancy
A1-1-2016200
A2-1-2016220
A3-1-2016220
A4-1-2016200
A5-1-2016180
B1-1-2016150
B2-1-2016175
B3-1-2016180
B4-1-2016180
B5-1-2016170

What I'm looking for is

ItemnrDateOccupancy
A2-1-2016220
B3-1-2016180

Function does work for itemnr with only 1 date with the max occupancy. Function so far is;

Firstsortedvalue(Date,-Aggr(sum(Occupancy),Date)

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Tom,

               I don't like the FirstSortedValue function as it returns null if there is a tie for the place you're looking for.

Try this expression:

Min(if(Aggr(Rank(Sum(Occupancy),1), Itemnr,Date) = 1,Date))

gives this:

Itemnr FirstDateFullOcc
A2-1-2016
B3-1-2016

Cheers

Andrew

View solution in original post

10 Replies
MK_QSL
MVP
MVP

SET DateFormat='D-M-YYYY';

or

SET DateFormat='M-D-YYYY';

Max(Aggr(SUM(Occupancy),Itemnr,Date))

and

FirstSortedValue(DISTINCT Date,  -Aggr(SUM(Occupancy),Itemnr,Date))

effinty2112
Master
Master

Hi Tom,

               I don't like the FirstSortedValue function as it returns null if there is a tie for the place you're looking for.

Try this expression:

Min(if(Aggr(Rank(Sum(Occupancy),1), Itemnr,Date) = 1,Date))

gives this:

Itemnr FirstDateFullOcc
A2-1-2016
B3-1-2016

Cheers

Andrew

MK_QSL
MVP
MVP

You can avoid this by using

FirstSortedValue( Distinct .....

HirisH_V7
Master
Master

Hi,

May be this,

Aggr(Max(Occupancy),Itemnr)

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
effinty2112
Master
Master

Tom,

               I forgot something, add the expression

Max(Aggr(Sum(Occupancy), Itemnr,Date))

to get the Occupancy figure,

Itemnr FirstDateFullOcc Occupancy
A2-1-2016220
B3-1-2016180

Cheers

Andrew

sunny_talwar

Or may be like this to pick minimum date for the maximum occupany

=FirstSortedValue(Date, -Aggr(Sum(Occupancy) - Date/1E6, Itemnr, Date))


Capture.PNG

sunny_talwar

But I guess Andrew's concern is that it will randomly pick from one of the two options. But in this case we know that OP wants to pick the minimum date from the maximum occupancy. So, I think we can code that extra bit in there

=FirstSortedValue(Date, -Aggr(Sum(Occupancy) - Date/1E6, Itemnr, Date))

And this should now always give one value, right?

effinty2112
Master
Master

Hi Manish,

You're right of course for this particular problem. My dislike for this function is based on a more general consideration of how it treats ties and that shouldn't have blinded me to using it.

If the problem was a different one such as wanting to see all the dates, rather than just the first, for which max occupancy was achieved then FirstSortedValue would fail.

Concat(if(Aggr(Rank(Sum(Occupancy),1), Itemnr,Date) = 1,Date),', ')

would return the dates that tie for first place.

Of course for the problem at hand FirstSortedValue with the DISTINCT qualifier may be more to Tom's taste.

Kind regards

Andrew

tomhovens
Contributor II
Contributor II
Author

Thanks a lot Andrew, Maresh and Sunny

Bot solutons does work!