topic Re: In between counts for dates in QlikView

In between counts for dates

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

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.

Re: In between counts for dates

Anil_Babu_Samineni — Wed, 14 Jun 2017 17:01:02 GMT

Can you try this?

Master Calendar Generation Script

Re: In between counts for dates

neelamsaroha157 — Wed, 14 Jun 2017 17:03:51 GMT

Try using interval match.

IntervalMatch

QlikView IntervalMatch

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 17:04:45 GMT

I already have a master calendar..

Will this be 2nd master calendar?

Re: In between counts for dates

neelamsaroha157 — Wed, 14 Jun 2017 17:05:19 GMT

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

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 17:09:43 GMT

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?

Re: In between counts for dates

Anil_Babu_Samineni — Wed, 14 Jun 2017 17:11:12 GMT

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?

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 17:16:21 GMT

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

Re: In between counts for dates

neelamsaroha157 — Wed, 14 Jun 2017 17:22:53 GMT

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

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 17:49:18 GMT

Hi Neelam,

PFA,

I'll modify it further if required.

thanks.

Re: In between counts for dates

neelamsaroha157 — Wed, 14 Jun 2017 18:29:18 GMT

Check this out.

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 18:47:59 GMT

Thanks for the effort but

the data which I provided seem to be incorrect for year and month,,,

the below attachment is from my dev data..

I picked 1 emp

who has 8 records in fact but 54 records in emp table.

If I select Apr 2015 then he should still show as 1 count though he has no entry for Apr-2015.... which I don't seem to see in your previous attachment.

EDIT:

Please check the qvw, file...

I was able to generate the dates... let me know if that is correct way?

Re: In between counts for dates

zebhashmi — Wed, 14 Jun 2017 19:47:37 GMT

I think what you need to do is

End Date - start Date

and chance the deference of days in to months

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 19:49:27 GMT

Yes, but I'd like to know how to get that in between dates in master or fact table.

so that I won't miss the counts

Re: In between counts for dates

Anonymous — Wed, 14 Jun 2017 19:55:07 GMT

Check out this.

Creating Reference Dates for Intervals

Re: In between counts for dates

zebhashmi — Wed, 14 Jun 2017 19:58:56 GMT

O Ok than may be you can do that count the master calendar from start date to end date

count({<Date= {....}>}MasterCD)

May be

Thankx

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 20:03:40 GMT

I checked it but not much of help...

Thanks though.

Re: In between counts for dates

neelamsaroha157 — Wed, 14 Jun 2017 20:49:03 GMT

Check this if it meets your requirements

Re: In between counts for dates

MK9885 — Wed, 14 Jun 2017 21:20:43 GMT

Yes, I suppose.
Basically your file is filling all the missing dates for Fact?

03/23/2013

03/22/2014

03/21/2015

01/01/2016

02/20/2016

03/19/2016

06/25/2016

03/18/2017

03/23/2013 till 03/22/2014 and 03/23/2014 till 03/21/2015, 03/22/2015 to 01/01/2016 and so on....?

why does it have only 2 years? 2013 and 2014 in Year field.

Isn't it suppose to have 2013, 2014, 2015, 2016 and 2017?

If we see I don't need STDATE...

I just need to populate missing dates from Fact

Like check the previous date and next date, populate missing dates...

Again, I really appreciate your help. Thanks

Re: In between counts for dates

neelamsaroha157 — Thu, 15 Jun 2017 13:48:34 GMT

How about this...