Find max effective date based on selected date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have some data here that need to be display as of it's effective date based on user selected date. I tried with something like...

Sum( {<EffDt={">=$(=max(EffDt)) <='01/12/2016'"}>} HeadCount) where '01/12/2016' will be some variable of user's selected date but it did not work.

Sample Data:

EffDt	Location	HeadCount
01 Jan 2016	A001	8
01 Jan 2016	A002	10
01 Jan 2017	A001	15
01 Jan 2017	A002	15

If user select Apr 2016 then the result should be:

EffDt	Location	HeadCount
01 Jan 2016	A001	8
01 Jan 2016	A002	10

If user select Feb 2017 then the result should be:

EffDt	Location	HeadCount
01 Jan 2017	A001	15
01 Jan 2017	A002	15

I'm new to Qlikview and not sure if it is possible to do so. Any advice on how to write expression will be very appreciated.

Thank you in advance.

topic Find max effective date based on selected date in QlikView

Find max effective date based on selected date