Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

any suggestion plz

swuehl
MVP
MVP

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
Specialist
Specialist
Author

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

swuehl
MVP
MVP

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
Specialist III
Specialist III

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