topic Re: Problem with firstsortedvalue in QlikView

Problem with firstsortedvalue

tomhovens — Wed, 26 Oct 2016 08:06:14 GMT

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)

Re: Problem with firstsortedvalue

MK_QSL — Wed, 26 Oct 2016 08:33:10 GMT

SET DateFormat='D-M-YYYY';

SET DateFormat='M-D-YYYY';

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

and

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

Re: Problem with firstsortedvalue

effinty2112 — Wed, 26 Oct 2016 09:58:52 GMT

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

Re: Problem with firstsortedvalue

MK_QSL — Wed, 26 Oct 2016 10:02:21 GMT

You can avoid this by using

FirstSortedValue( Distinct .....

Re: Problem with firstsortedvalue

HirisH_V7 — Wed, 26 Oct 2016 10:03:05 GMT

Hi,

May be this,

Aggr(Max(Occupancy),Itemnr)

-Hirish

Re: Problem with firstsortedvalue

effinty2112 — Wed, 26 Oct 2016 10:05:07 GMT

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

Re: Problem with firstsortedvalue

sunny_talwar — Wed, 26 Oct 2016 10:06:49 GMT

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

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

Re: Problem with firstsortedvalue

sunny_talwar — Wed, 26 Oct 2016 10:11:56 GMT

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?

Re: Problem with firstsortedvalue

effinty2112 — Wed, 26 Oct 2016 10:19:23 GMT

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

Re: Problem with firstsortedvalue

tomhovens — Wed, 26 Oct 2016 10:21:53 GMT

Thanks a lot Andrew, Maresh and Sunny

Bot solutons does work!

Re: Problem with firstsortedvalue

Anonymous — Tue, 02 May 2017 06:35:30 GMT

Hi Sunny,

In the above expression, I couldn't understand the below part alone

Date/1E6