topic Re: Help with Date's in QlikView

Help with Date's

Fri, 08 Aug 2014 12:46:42 GMT

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

Re: Help with Date's

Fri, 08 Aug 2014 12:51:09 GMT

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

Re: Help with Date's

Fri, 08 Aug 2014 13:14:59 GMT

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.

Re: Help with Date's

Fri, 08 Aug 2014 14:47:00 GMT

May be like attached.

Hope it helps

Thanks

Re: Help with Date's

Fri, 08 Aug 2014 16:52:29 GMT

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