# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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.

`LOADAd_ID,StartDate,EndDateFROM 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/2011Ad_ID 1 - StartDate=10/01/2011; EndDate=20/01/2011; DaysInRange should = 11Ad_ID 2 - StartDate=20/12/2010; EndDate=02/01/2011; DaysInRange should = 2Ad_ID 3 - StartDate=10/11/2010; EndDate=20/12/2010; DaysInRange should = 0Ad_ID 4 - StartDate=05/02/2011; EndDate=20/02/2011; DaysInRange should = 0Ad_ID 5 - StartDate=20/01/2011; EndDate=20/02/2011; DaysInRange should = 12Ad_ID 6 - StartDate=01/12/2010; EndDate=01/03/2011; DaysInRange should = 31Any ideas!?Thank you,Jason </body>`
Tags (3)
1 Solution

Accepted Solutions
Valued Contributor III

## How many days fall between 2 dates?

Rakesh

`Ads:LOAD * INLINE [Ad_ID, StartDate, EndDate1, 1/1/2010, 5/1/20102, 1/3/2011, 1/17/20113, 1/1/2010, 12/31/20114, 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 DaysRESIDENT Ads;`

5 Replies
New Contributor III

## How many days fall between 2 dates?

Did you try the function networkdays?

Valued Contributor III

## How many days fall between 2 dates?

Rakesh

`Ads:LOAD * INLINE [Ad_ID, StartDate, EndDate1, 1/1/2010, 5/1/20102, 1/3/2011, 1/17/20113, 1/1/2010, 12/31/20114, 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 DaysRESIDENT Ads;`

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.

Honored Contributor II

## How many days fall between 2 dates?

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

Jason

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