In between counts for dates

[MK9885]

Hello,

I've a requirement to count a record by range based.

Ex:

Fact:

EMP     StartDate     EMPname    Status    

01       02/01/2014     ABC             Available

01       03/01/2017     BCD             Offline

In above scenario the fact has 2 entry for EMP01 for 02/2014 and 03/2017

So if I do counts it will come as 2.

But he is available through out 02/2014 till 03/2017.

So if I select 01/2015 or 05/2016 he should still be counted in that month.

My Fact is linked to master based on Datekey which is 20140201 and 20170301

I need to calculate based on range and also count him from 2014, 2015, 2016 and 2017 till March 01.

If I select any year or any month in between 2014 and 2017 I should get an entry, which I'm not getting now.

Can someone please help me calculating range based?

stalwar1‌ neelamsaroha1575‌

Thanks.

[Anil_Babu_Samineni]

Can you try this?

Master Calendar Generation Script

[neelamsaroha157]

Try using interval match.

IntervalMatch

QlikView IntervalMatch

[MK9885]

I already have a master calendar..

Will this be 2nd master calendar?

[neelamsaroha157]

Try creating Min & max date per Emp, use those dates as Start & End Date using Your actual date in Interval Match.

[MK9885]

sorry, can you post and example script based upon my given example?

I'm doing this Int Match based on that link..but it would be helpful if you give an example?

[Anil_Babu_Samineni]

Let's consider for 1 Year data in your sample calendar. Is you have 365 dates then what are you expecting the value for count of Employee?

[MK9885]

It should be one.

But for any month or year he should show 1 record in front end.. I can take distinct count

But there are only 2 entries in fact from 2013 to 2017 and he was also available in 2014, 2015 and 2016 and dates don't show.

so for each year or month he should be counted as 1 until 2017

[neelamsaroha157]

It would be helpful if you can share some sample data.

[MK9885]

Hi Neelam,

PFA,

I'll modify it further if required.

thanks.