topic Re: Counting working days this week / month in App Development

Counting working days this week / month

bwisealiahmad — Tue, 29 Nov 2016 15:19:59 GMT

So in an excel sheet there is a column called "Days this week" which counts:

Monday as 1,

Tuesday as 2,

Wednesday as 3

(only Mon-Fri)

And another column called Days This Month that also works as a counter of working days from the first day of the month as 1, second as 2 etc. and want this to automatically be done for each month.

I want to create these fields based on a budget date.

I've heard about "Network" days, but not sure how to get to this.

Any tips anyone?

Re: Counting working days this week / month

rubenmarin — Tue, 29 Nov 2016 15:30:43 GMT

Hi Ali, probably you heard about networkdays(): networkdays ‒ QlikView

And it's true , I think is what you need, check the help, is easy to underdstand how it works.

Nothe that you can use a variable to store the holiday days and use in the function, in example based on today date:

SET vHolidays = "'01/11/2016', '08/12/2016'";

=NetWorkDays(Monthstart(Today()), MonthEnd(Today()), $(vHolidays))

Re: Counting working days this week / month

sunny_talwar — Tue, 29 Nov 2016 15:33:50 GMT

May be like this:

Table:

LOAD *,

Month(Date) as Month,

Year(Date) as Year,

If(not Match(WeekDay(Date), 'Sun', 'Sat'), Num(WeekDay(Date)), 0) as [Day this week],

WeekDay(Date) as WeekDay,

If(MonthName(Date) = Previous(MonthName(Date)), If(not Match(WeekDay(Date), 'Sun', 'Sat'), RangeSum(Peek('Day this month'), 1), RangeSum(Peek('Day this month'), 0)), If(not Match(WeekDay(Date), 'Sun', 'Sat'), 1, 0)) as [Day this month];

LOAD Date(MakeDate(1999, 12, 31) + IterNo()) as Date

AutoGenerate 1

While IterNo() <= 7300;

Re: Counting working days this week / month

bwisealiahmad — Wed, 30 Nov 2016 08:58:43 GMT

Yes that is how I want it, but I can't seem to adapt it to my model. This is the budget load script part:

Budget:

LOAD

Date(PaidOutBookDate) AS BudgetDate,

Actual,

Budget

FROM [lib://99.Shared_Folders/2.QVD\Budget.qvd]

(qvd);

Re: Counting working days this week / month

sunny_talwar — Wed, 30 Nov 2016 09:02:46 GMT

Something like this:

FactTable:

LOAD

Date(PaidOutBookDate) AS BudgetDate,

Actual,

Budget

FROM [lib://99.Shared_Folders/2.QVD\Budget.qvd]

(qvd);

DateTable:

LOAD BudgetDate,

Month(BudgetDate) as Month,

Year(BudgetDate) as Year,

If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat'), Num(WeekDay(BudgetDate)), 0) as [Day this week],

WeekDay(BudgetDate) as WeekDay,

If(MonthName(BudgetDate) = Previous(MonthName(BudgetDate)), If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat'), RangeSum(Peek('Day this month'), 1), RangeSum(Peek('Day this month'), 0)), If(not Match(WeekDay(Date), 'Sun', 'Sat'), 1, 0)) as [Day this month]

Resident FactTable

Order By BudgetDate;

Re: Counting working days this week / month

bwisealiahmad — Wed, 30 Nov 2016 09:42:06 GMT

It is working for month, but not for weekdays. It seems like the budgetdate doesn't include saturday and sunday and that is messing it up. See screenshot.

Re: Counting working days this week / month

sunny_talwar — Wed, 30 Nov 2016 09:45:00 GMT

I guess you just need to add 1 to fix this issue

If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat'), RangeSum(Num(WeekDay(BudgetDate)), 1), 0) as [Day this week],

Re: Counting working days this week / month

bwisealiahmad — Wed, 30 Nov 2016 10:11:41 GMT

Thanks for all the help Sunny. This looks good!

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 10:14:12 GMT

Hi Sunny,

A follow up question.

If I want to exclude some dates from the counting because they are holidays, is there any way to do this? Either add a variable and have it not count the days that are in that variable or something?

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 10:18:49 GMT

I guess you can add another if statement in there to do this, right? for instance you want to exclude 1st Jan and 1st Feb ->

If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat') and not Match(BudgetDate, MakeDate(2016, 1, 1), MakeDate(2016, 2, 1)), RangeSum(Num(WeekDay(BudgetDate)), 1), 0) as [Day this week],

There might be a better way to do this, but I am not sure I know another way....

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 10:22:42 GMT

Ok...and same for Day This Month I guess?

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 10:30:46 GMT

I believe so... I guess test it out and see if it is working or not

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 10:43:08 GMT

It almost works, except not it is resetting the days this month counter:

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 10:45:58 GMT

Can you try this:

If(MonthName(BudgetDate) = Previous(MonthName(BudgetDate)),

If(not Match(WeekDay(BudgetDate), 'Sun', 'Sat') and not Match(BudgetDate, MakeDate(2016, 1, 1), MakeDate(2016, 2, 1)), RangeSum(Peek('Day this month'), 1), RangeSum(Peek('Day this month'), 0)), If(not Match(WeekDay(Date) and not Match(BudgetDate, MakeDate(2016, 1, 1), MakeDate(2016, 2, 1)), 'Sun', 'Sat'), 1, 0)) as [Day this month]

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 11:11:49 GMT

I think this will work...

It is adding 17 twice and continuing the count so that way the max is 21 instead of 22 because of that.

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 11:28:11 GMT

On week tho there is a 0 now and it starts on 2.

It is the Peek that is doing so that it starts on the correct number or how is that? (scroll all the way to the right to see the picture)

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 11:46:41 GMT

that is strange, let me check this at my end

Re: Counting working days this week / month

bwisealiahmad — Tue, 06 Dec 2016 11:49:47 GMT

Ok right now for week it is:

If(not Match(WeekDay(BudgetDate), 'sun', 'sat') and not Match(BudgetDate,

MakeDate(2016, 12, 26)), RangeSum(Num(WeekDay(BudgetDate)), 1), 0) as [Day This Week],

If that helps.

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 11:52:07 GMT

My guess is that you are using Dec 26th as the holiday? Wouldn't you want to see the same number repeated? What else do you want to see?

Re: Counting working days this week / month

sunny_talwar — Tue, 06 Dec 2016 11:52:32 GMT

Have you resolved the month part?