Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (1)
1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Problem with firstsortedvalue

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

10 Replies

Re: Problem with firstsortedvalue

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
Honored Contributor

Re: Problem with firstsortedvalue

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

Re: Problem with firstsortedvalue

You can avoid this by using

FirstSortedValue( Distinct .....

hirishv7
Honored Contributor

Re: Problem with firstsortedvalue

Hi,

May be this,

Aggr(Max(Occupancy),Itemnr)

-Hirish

effinty2112
Honored Contributor

Re: Problem with firstsortedvalue

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

MVP
MVP

Re: Problem with firstsortedvalue

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

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


Capture.PNG

MVP
MVP

Re: Problem with firstsortedvalue

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
Honored Contributor

Re: Problem with firstsortedvalue

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
New Contributor II

Re: Problem with firstsortedvalue

Thanks a lot Andrew, Maresh and Sunny

Bot solutons does work!