Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Interval in DD HH:MM just excluding 'Sunday'

Greetings all,

I have a small issue, I want to calculate the interval between 2 dates but excluding the Sunday, not all the weekend. So far I had been using the 'NetWorkDay()' function but this one is excluding Sunday and Saturday too. Saturday I want it in the calculation.

So the result that I want should be in Days Hours:Minutes (interval)

Anyone has any idea how I can do this ?

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Interval in DD HH:MM just excluding 'Sunday'

Maybe like

Interval(

     rangesum(

          DT2-DT1,

          -If(Weekday(DT1)=6,1-frac(DT1)),

          -If(WeekDay(DT2)=6,frac(DT2)),

          -Div(floor(DT2)-floor(DT1),7), -If(WeekDay(DT2)<Weekday(DT1),1),  if(WeekDay(DT1)=6,1)

     )

)

edit:

added a component to correct Sunday counting

11 Replies
MVP
MVP

Re: Interval in DD HH:MM just excluding 'Sunday'

You can look into one of the approaches shown here:

Calculate hours between two Date/Time strings

They may be a bit more complex than requested, but it should be quite easy to simplify and adapt.

Highlighted
Not applicable

Re: Interval in DD HH:MM just excluding 'Sunday'

Yes, I know that post, but I did not find anything that can help on my situation.

MVP
MVP

Re: Interval in DD HH:MM just excluding 'Sunday'

Do you actually have start and end dates or timestamps?

Not applicable

Re: Interval in DD HH:MM just excluding 'Sunday'

Time stamps. Sorry that I did not mention it.

Not applicable

Re: Interval in DD HH:MM just excluding 'Sunday'

But I have start time stamp and end time stamp.

MVP
MVP

Re: Interval in DD HH:MM just excluding 'Sunday'

Here is adapted sample code from the referenced thread:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM  

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, frac(DT1), 0) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), frac(DT2),1) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP

where WeekDay(Date)<6 and not match(Date,$(vHol))  // Exclude Sundays and Holidays defined above

group by ID;

drop table TMP;

Not applicable

Re: Interval in DD HH:MM just excluding 'Sunday'

Thanks! But there is any way to do this into a chart ? Not in the script.But somehow to exclude the sundays ? Like this example ?

=Interval(

          (RangeMin(frac(Closedate), MakeTime(18))

          - RangeMax(frac(Logdate), MakeTime(8)))

          + (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)


MVP
MVP

Re: Interval in DD HH:MM just excluding 'Sunday'

Maybe like

Interval(

     rangesum(

          DT2-DT1,

          -If(Weekday(DT1)=6,1-frac(DT1)),

          -If(WeekDay(DT2)=6,frac(DT2)),

          -Div(floor(DT2)-floor(DT1),7), -If(WeekDay(DT2)<Weekday(DT1),1),  if(WeekDay(DT1)=6,1)

     )

)

edit:

added a component to correct Sunday counting

Not applicable

Re: Interval in DD HH:MM just excluding 'Sunday'

Is not working properly.

This is my script where I calculate only the work days.

Interval(

          (RangeMin(frac(Real_Resolution_Provided_to_Client__c))

          - RangeMax(frac(Open_Date__c)))

          + (NetWorkDays(Open_Date__c, Real_Resolution_Provided_to_Client__c))

,'D hh:mm')

Do you have any idea in order to change this one to take the Saturdays ?