How to display only 1 record instead all (expressions)

[#1]

Dear all,

I find it difficult to get only the 1 record base on my period selection.

In following casse, the period is 30 of november 2012, i wish to get result only the 16/11/2012 instead of 01/04/2011.

In other words, the top pccupncy date to the period i selected.

Billion thanks to you, if you can help on this.

[khadeer]

firstsortedvalue(Total Building,Occupncy)

hope it will help u.

Regards,

Khadeer

[#3]

firstsortedvalue(Total Building,Occupncy) doesn't work well if i have multiple records which consists of many suitid there.

I hope to select only the closest to my period selection in this case nov 2012. in my data might consists other suitid too. so all of the suitid records will use thier own one with closest Occuncy date as output.

the logic behind for sql is something like

select top 1 buildingNLA

where occupncy <= period

order occupncy desc

[#4]

Hi in ur expression add this condition too.

Sum(if(isnull(somthing) and OCCPNCY=Max(OCCPNCY),'True','False'))

~Kabilan K

[#5]

Hi, Kabilan & QV learner

Thanks for your reply.. The concept for the expressions is basically like following sql statement,

select top 1 buildingNLA

where occupncy <= period

order occupncy desc

Hi, Kabilan

The script you shared is not working and error. Please correct me if i were wrong.

=

sum(if(IsNull(OCCUPNCY)=0 AND Max(OCCUPNCY)='True','False',BuildingNLA

))

[#6]

I modify a bit "QV Learner" script, it seen work and i use total sum of the row to display the Total.

Aggr

(FirstSortedValue(BuildingNLA,-[OCCUPNCY]),SUITID2)

Now, the issue left the OCCUPNCY date will change base on what i have select in my period value.