Help with Date's

[#1]

I am a newbie! The problem I have is as follows:

I have 4 rows:

UserName Deparrtment Effectivedt Expiration dt

Jayc             111              01/01/1900  07/22/2014

JayC             11 2             07/23/2014  08/01/2014

JayC             110              08/02/2014  12/31/9999

JohnS           111              06/01/2014  06/10/2014

I want to do a count of active users as of certain date (Report date). So if report date is 07/28/2014, I should return 1 active user, but if report date is 06/05/2014, the query should return 2 active users.

Basically the predicate should be: Report_date between effective and expiration dt. How can I translate that in Qlikview for doing counts?

Thank you in advance

[#2]

Have you looked at using IntervalMatch? Henric does a good job of explaining the usage here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

[#3]

Thanks for posting the link. This was really informative. I am joining multiple SCD's and would like to let the user choose the single date (Report date) for which report has been requested for. This date would then do in between effective and expiration.

[#4]

May be like attached.

Hope it helps

Thanks

AJ

[#5]

Thank you for all your replies. This is a great forum. I believe I need a combination of above two solutions. In my specific case I have two SCD tables (table A and Table B), with effective and expiration dates. I am looking for a count of Attribute 1 from table A, with an inner join to table B which is valid during the reporting timeframe.

Thank you for all your help