Problem with firstsortedvalue

[tomhovens]

Hey All,

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

Itemnr	Date	Occupancy
A	1-1-2016	200
A	2-1-2016	220
A	3-1-2016	220
A	4-1-2016	200
A	5-1-2016	180
B	1-1-2016	150
B	2-1-2016	175
B	3-1-2016	180
B	4-1-2016	180
B	5-1-2016	170

What I'm looking for is

Itemnr	Date	Occupancy
A	2-1-2016	220
B	3-1-2016	180

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

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

[MK_QSL]

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 · Accepted solution]

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
A	2-1-2016
B	3-1-2016

Cheers

Andrew

[MK_QSL]

You can avoid this by using

FirstSortedValue( Distinct .....

[HirisH_V7]

Hi,

May be this,

Aggr(Max(Occupancy),Itemnr)

-Hirish

[effinty2112]

Tom,

               I forgot something, add the expression

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

to get the Occupancy figure,

Itemnr	FirstDateFullOcc	Occupancy
A	2-1-2016	220
B	3-1-2016	180

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

[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]

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]

Thanks a lot Andrew, Maresh and Sunny

Bot solutons does work!