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?

[bwisealiahmad]

The month part is correct because it is now 21 days that is counted (the max is 21 now because 26th is deducted)

but for that specific week where 26th is part of it is still saying there are 5 workdays that week, when in reality it is 4. Makes sense?

Instead of counting 27th as day #2 that week it should be day #1. and then continue counting days for that week up until 4 and not 5 because 26th is excluded from that week.

[sunny_talwar]

Try this:

If(not Match(WeekDay(Date), 'Sun', 'Sat'),

If(not Match(Date, MakeDate(2016, 12, 26)), RangeSum(Peek('Day this week'), 1), RangeSum(Peek('Day this week'), 0)), 0) as [Day this week],

[bwisealiahmad]

Hmmm made it act a bit weird. Not is it counting from the beginning of the dataset instead of starting from the beginning of each week.

[sunny_talwar]

Did you have the first part of the if statement?

If(not Match(WeekDay(Date), 'Sun', 'Sat'),

If(not Match(Date, MakeDate(2016, 12, 26)), RangeSum(Peek('Day this week'), 1), RangeSum(Peek('Day this week'), 0)), 0) as [Day this week],

[bwisealiahmad]

Yes Just did a copy paste. Same result...weird.

[sunny_talwar]

Can I send you my qvw or you only have qlik sense?

[bwisealiahmad]

Send it. i have QV also installed.

[sunny_talwar]

See if you can make out any differences. Or else share your qvf and I can check it out

[bwisealiahmad]

I honestly can't. Do you mind if I add you on Skype?

[sunny_talwar]

I am at work now (won't be home until another 8 hrs)