topic How many days fall between 2 dates? in QlikView

How many days fall between 2 dates?

Jason_Michaelides — Sat, 12 Mar 2011 20:25:42 GMT

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>

How many days fall between 2 dates?

jbeierschmitt — Sat, 12 Mar 2011 22:09:07 GMT

Did you try the function networkdays?

How many days fall between 2 dates?

disqr_rm — Sat, 12 Mar 2011 22:28:20 GMT

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;

How many days fall between 2 dates?

Jason_Michaelides — Sun, 13 Mar 2011 20:37:06 GMT

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

How many days fall between 2 dates?

Jason_Michaelides — Sun, 13 Mar 2011 20:37:56 GMT

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

Jason

How many days fall between 2 dates?

disqr_rm — Sun, 13 Mar 2011 23:37:32 GMT

Thanks Jason, it's nice to be appreciated.

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

Rakesh