Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
firstsortedvalue(Total Building,Occupncy)
hope it will help u.
Regards,
Khadeer
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
Hi in ur expression add this condition too.
Sum(if(isnull(somthing) and OCCPNCY=Max(OCCPNCY),'True','False'))
~Kabilan K
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
))
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.