Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to display only 1 record instead all (expressions)

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.

03-01-2013 12-21-27 PM.png

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

5 Replies
khadeer_sparks
Valued Contributor

Re: How to display only 1 record instead all (expressions)

firstsortedvalue(Total Building,Occupncy)

hope it will help u.

Regards,

Khadeer

Not applicable

Re: How to display only 1 record instead all (expressions)

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

Not applicable

Re: How to display only 1 record instead all (expressions)

Hi in ur expression add this condition too.

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

~Kabilan K

Not applicable

Re: How to display only 1 record instead all (expressions)

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

))

Not applicable

Re: How to display only 1 record instead all (expressions)

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.