Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

edp
Contributor

Networkdays with saturday

Hello,

I have to count how many working days there are between two dates, but I have to consider that Saturday is ALWAYS a working day, so I cant's use the networkingdays() function, which excludes Saturday. At the same time, I can't use a calendar because I have to use the calculation for many occurrences in various years, so it should be too difficult for me to load a so long calendars.

Can anyone suggest an idea to me?

Thank you for interesting.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

Ah, ehm, you can't. Not without using a calendar, which you don't want to do. You could try adding a vbscript function in the macro editor:

Function GetWorkDays(StartDate, EndDate)

    dCount = 0

    For dt = StartDate To EndDate

        If WeekDay(dt) <> 1 Then

            dCount = dCount + 1

        End If

    Next

    GetWorkDays = dCount

End Function

And use that function in the load script. Make sure the dates that are passed to the function are numeric dates.

GetWorkDays(StartDate,EndDate) as WorkingDays


talk is cheap, supply exceeds demand

View solution in original post

14 Replies
MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

You could add a new field in the script: if(weekday(MyDate)<>6,1,0) as WorkDay. You can then sum the workday field to get the number of workdays. If you don't want to create a new field you can use the if statement in an expression in your chart.


talk is cheap, supply exceeds demand
edp
Contributor

Re: Networkdays with saturday

Sorry but I didn't understand.

This is the part of my script when I apply the function:

LOAD

    Date(StartDate,'DD/MM/YY') As StartDateFormatted,

    Date(EndDate,'DD/MM/YY') As EndDateFormatted,

    networkdays (DataInizio, DataFine) As WorkingDays;

SQL SELECT

    IdPersona,

    StartDate,

    EndDate,

FROM Table;

How can I apply your suggestion?

Thanks

MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

Ah, ehm, you can't. Not without using a calendar, which you don't want to do. You could try adding a vbscript function in the macro editor:

Function GetWorkDays(StartDate, EndDate)

    dCount = 0

    For dt = StartDate To EndDate

        If WeekDay(dt) <> 1 Then

            dCount = dCount + 1

        End If

    Next

    GetWorkDays = dCount

End Function

And use that function in the load script. Make sure the dates that are passed to the function are numeric dates.

GetWorkDays(StartDate,EndDate) as WorkingDays


talk is cheap, supply exceeds demand

View solution in original post

edp
Contributor

Re: Networkdays with saturday

Great!!

Thank you very much.

N.

MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

Hi N.

since there is no function for script (except Marco) I would prefer a calculation in script:

Load

floor(((Date2 - Date1)+1)/7)*6 + mod((Date2 - Date1)+1,7)

+ if(Weekday(Date1) + mod((Date2 - Date1)+1,7) < 6, 0, -1) as Workingdays_6

...

Please see atached example. Hope I have covered all variations. Maybe there is a possibility of simplification ..

- Ralf

edp
Contributor

Re: Networkdays with saturday

Hello,

Thank you for your solution, but it dosn't work properly:

12/12/12 - 22/12/12 Working days: 10 Your function: 9

17/12/12 - 22/12/12 Working days: 6 Your function: 5

20/12/12 - 22/12/12 Working days: 3 Your function: 2

Have a nice WE.

N.

MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

Small mistake..

Load

floor(((Date2 - Date1)+1)/7)*6 + mod((Date2 - Date1)+1,7)

+ if(Weekday(Date1) + mod((Date2 - Date1)+1,7) < 7, 0, -1) as Workingdays_6

...

Have a good one!

- Ralf

edp
Contributor

Re: Networkdays with saturday

Yeah !!

My best compliments for your ability with math! You're a sort of genius with the numbers...

Thank to both of you.

N.

MVP & Luminary
MVP & Luminary

Re: Networkdays with saturday

Thx, this is just basic stuff. I'm not that math geek 😉

But a good exercise...