Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to count sundays

Hello every one i need a  suggestion on how to calculate actual days required for vehicles to reach at destination point including SUNDAY's.

  

if Vehicle-1 requires 14 days to reached at destination and in-between 14 days if 2 sundays count is there so 

actual days required for vehicle-1 to reached at destination point is 16 Days.

Vehicle No.    Gate Out Date         Require Days to Reach at Destination

A               02-01-2015                              5

B               07-01-2015                             23

C                  10-01-2015                          12

D                  22-01-2015                            7

6 Replies
kunkumnaveen
Contributor III

Re: how to count sundays

Hello,

what i am looking is

let say vehicle-A required 5 days to reach destination  which means

Gate Out Date  of vehicle-A is on 02-01-2015 and it will take 5 days to reach destination

but when i show count for actual days for vehicle -A  then it will be 6 days because there is a Sunday in between

so how to count Sundays

kunkumnaveen
Contributor III

Re: how to count sundays

any suggestion plz

MVP
MVP

Re: how to count sundays

Maybe something like this?

Set DateFormat = 'DD-MM-YYYY';

LOAD *,

[Gate Out Date]+[Require Days to Reach at Destination]+Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7) as Est.Arrival;

LOAD * INLINE [

Vehicle No.,    Gate Out Date,         Require Days to Reach at Destination

A,               02-01-2015,                              5

B,               07-01-2015,                             23

C,                  10-01-2015,                          12

D,                  22-01-2015,                            7

];

kunkumnaveen
Contributor III

Re: how to count sundays

HI,

[Gate Out Date]+[Require Days to Reach at Destination]+Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7) as Est.Arrival;


can u explain me what exactly this one is doing plzzz

thanks

MVP
MVP

Re: how to count sundays

It's supposed to calculate the date when the vehicle reaches destination, taking sundays into account.

You may want to enclose above in a Dayname() function to format the result of the calculation as date.

The first two operands are pretty simple, [Get Out Date] + [Require Days to Reach at Destination], that should return the arrival date not taking sundays into account.

Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7)

tries to calculate the number of sundays you need to consider. Have a look at Weekday() and Div() function, this should make it clearer.

edit:

Just noticed that you may need to add +1, like

Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date])+1,7)

rahulpawarb
Valued Contributor II

Re: how to count sundays

Hello Naveen,

Trust that you are doing good!

Please refer below given sample script:

Data:

LOAD *,

     [Require Days to Reach at Destination] + Ceil(([Require Days to Reach at Destination] - NetWorkDays)/2) As [Actual Days to Reach at Destination];

LOAD *,

     NetWorkDays([Gate Out Date], [Reached at Destination]) AS NetWorkDays;

LOAD *,

     Date([Gate Out Date] + [Require Days to Reach at Destination], 'DD-MM-YYYY') AS [Reached at Destination];

LOAD

     [Vehicle No.],

     Date#([Gate Out Date], 'DD-MM-YYYY') AS [Gate Out Date],

     [Require Days to Reach at Destination];

LOAD * INLINE [

Vehicle No., Gate Out Date, Require Days to Reach at Destination

A, 02-01-2015, 5

B, 07-01-2015, 23

C, 10-01-2015, 12

D, 22-01-2015, 7

E, 01-01-2017, 45

];

Also refer the sample application attached herewith.

Regards!

Rahul

Community Browser