Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

In between counts for dates

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?

stalwar1neelamsaroha1575

Thanks.


1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
Specialist II

Check this.

Made changes to your excel because it had StartDate > ENDdate (I just Reversed them).

View solution in original post

36 Replies
Anil_Babu_Samineni

Can you try this?

Master Calendar Generation Script

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
neelamsaroha157
Specialist II
Specialist II

Try using interval match.

IntervalMatch

QlikView IntervalMatch

MK9885
Master II
Master II
Author

I already have a master calendar..

Will this be 2nd master calendar?

neelamsaroha157
Specialist II
Specialist II

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

MK9885
Master II
Master II
Author

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Master II
Master II
Author

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
Specialist II
Specialist II

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

MK9885
Master II
Master II
Author

Hi Neelam,

PFA,

I'll modify it further if required.

thanks.