Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jason_michaelid
Honored Contributor II

How many days fall between 2 dates?

Hi guys,

I have a problem I just can't get my head around - hoping you can help [8-|]

- At the top of my load script I set two variables - vMinDate and vMaxDate.
- I have a table of Adverts with campaign StartDates and EndDates.


LOAD
Ad_ID,
StartDate,
EndDate
FROM Ads.qvd(qvd);
[/Code]
I'd like to calculate, for each Advert, how many days of the campaign fall between vMinDate and vMaxDate (inclusive).
E.g. - vMinDate = 01/01/2011; vMaxDate = 31/01/2011
Ad_ID 1 - StartDate=10/01/2011; EndDate=20/01/2011; DaysInRange should = 11
Ad_ID 2 - StartDate=20/12/2010; EndDate=02/01/2011; DaysInRange should = 2
Ad_ID 3 - StartDate=10/11/2010; EndDate=20/12/2010; DaysInRange should = 0
Ad_ID 4 - StartDate=05/02/2011; EndDate=20/02/2011; DaysInRange should = 0
Ad_ID 5 - StartDate=20/01/2011; EndDate=20/02/2011; DaysInRange should = 12
Ad_ID 6 - StartDate=01/12/2010; EndDate=01/03/2011; DaysInRange should = 31
Any ideas!?
Thank you,
Jason
</body>
1 Solution

Accepted Solutions
disqr_rm
Valued Contributor III

How many days fall between 2 dates?

This should help you or at least give you an idea of possibilities. Please post your feedback.

Rakesh


Ads:
LOAD * INLINE [
Ad_ID, StartDate, EndDate
1, 1/1/2010, 5/1/2010
2, 1/3/2011, 1/17/2011
3, 1/1/2010, 12/31/2011
4, 5/1/2011, 5/5/2011
];

LET vMinDate = makedate(2011, 1, 1);
LET vMaxDate = makedate(2011, 1,31);

NewAds:
LOAD
Ad_ID,
if(EndDate < '$(vMinDate)' or StartDate > '$(vMaxDate)', 0, rangemin(EndDate, '$(vMaxDate)') - rangemax(StartDate, '$(vMinDate)')) as Days
RESIDENT Ads;




5 Replies
jbeierschmitt
New Contributor III

How many days fall between 2 dates?

Did you try the function networkdays?

disqr_rm
Valued Contributor III

How many days fall between 2 dates?

This should help you or at least give you an idea of possibilities. Please post your feedback.

Rakesh


Ads:
LOAD * INLINE [
Ad_ID, StartDate, EndDate
1, 1/1/2010, 5/1/2010
2, 1/3/2011, 1/17/2011
3, 1/1/2010, 12/31/2011
4, 5/1/2011, 5/5/2011
];

LET vMinDate = makedate(2011, 1, 1);
LET vMaxDate = makedate(2011, 1,31);

NewAds:
LOAD
Ad_ID,
if(EndDate < '$(vMinDate)' or StartDate > '$(vMaxDate)', 0, rangemin(EndDate, '$(vMaxDate)') - rangemax(StartDate, '$(vMinDate)')) as Days
RESIDENT Ads;




jason_michaelid
Honored Contributor II

How many days fall between 2 dates?

Thanks, but NetworkDays will only give working days and weekends are just as important for this.

jason_michaelid
Honored Contributor II

How many days fall between 2 dates?

Rakesh - you're a genius! Thank you so much that worked perfectly Big Smile

Jason

disqr_rm
Valued Contributor III

How many days fall between 2 dates?

Thanks Jason, it's nice to be appreciated.

Glad that it works and I could help. Good luck!!!

Rakesh

Community Browser