Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
bwisealiahmad
Not applicable

Counting working days this week / month

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?

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Counting working days this week / month

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;

53 Replies
rubenmarin
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

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.

Capture5.JPG

sunny_talwar
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

Thanks for all the help Sunny. This looks good!

bwisealiahmad
Not applicable

Re: Counting working days this week / month

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
Not applicable

Re: Counting working days this week / month

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