topic Calulate consecutive days.. in QlikView

Calulate consecutive days..

Thu, 05 Jan 2012 08:01:37 GMT

Hi Experts,

i need help to calculate difference between consecutive days.., below is the description..

If a call made Jan4 - wed, jan 5-thu,jan6- fri, jan7- sat, jan9 - Mon, Jan11- Tue.... the difference between the consecutive days is 1. if sunday is come in between consecutive days we need to ignore... And the difference between saterday and monday should be 1 not 2. we usually get the difference between saterday and monday is 2. but i need difference is 1..

Note: the mentiuoned dates are 2012 year dates... i mean current days for example.

Could any one help please.. Thanks in advance..

Regards,

Chandra

Calulate consecutive days..

swuehl — Thu, 05 Jan 2012 10:10:38 GMT

Hi Chandra,

you could use something along these lines:

Dates:

LOAD Date(makedate(2011)+recno()-1) as Date

AutoGenerate 396;

Calls:

LOAD CallDate

,if(WeekStart(CallDate)= weekstart(peek(CallDate)), CallDate-peek(CallDate), CallDate-peek(CallDate)- (weekstart(CallDate)-weekstart(peek(CallDate)))/7 ) as DiffCallDates ;

LOAD Date as CallDate resident Dates where weekday(Date) <6

;

drop table Dates;

i.e. if the dates cross week bounderies, you subtract number of weeks.

If you don't want to do it in the script, you could do something like

=if(WeekStart(CallDate)= weekstart(above(CallDate)), CallDate-above(CallDate), CallDate-above(CallDate)- (weekstart(CallDate)-weekstart(above(CallDate)))/7 )

also in a straight table chart with dimension CallDate sorted asc numerical.

Hope this helps,

Stefan

Calulate consecutive days..

Clever_Anjos — Thu, 05 Jan 2012 17:42:14 GMT

Does NetWorkDays function do what you need?

networkdays( start_date, end_date {, holiday} )

Returns the number of working days (Monday-Friday) between and

including start_date and end_date taking into account any optionally

listed holidays. All parameters should be valid dates or timestamps.

Example:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-

26') returns 8