Counting working days this week / month

[bwisealiahmad]

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?

[rubenmarin]

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))

[sunny_talwar]

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;

[bwisealiahmad]

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);

[sunny_talwar · Accepted solution]

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;

[bwisealiahmad]

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.

[sunny_talwar]

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],

[bwisealiahmad]

Thanks for all the help Sunny. This looks good!

[bwisealiahmad]

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?

[sunny_talwar]

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....