Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bwisealiahmad
Partner - Specialist
Partner - Specialist

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?

53 Replies
bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

It's fine. No worries.

You don't think because I have dates in different format that could affect this?

Your qvw is in MM/DD/YYYY my dataset is in DD.MM.YYYY

sunny_talwar

Oh I think I know the issue... I think the data you are using doesn't have any weekend dates which is what is causing the issue. Let me look into this again.

Try this:

If(Num(WeekDay(Date)) = 1,

  If(not Match(Date, MakeDate(2016, 12, 26)), 1, 0),

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

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

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Hmmm I tried and got this:

ResetsCounter.JPG

sunny_talwar

Can you change this:

SET FirstWeekDay=6;

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Finally! Works!

Thank you so much!

sunny_talwar

Awesome.... we finally made it through

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Hi Sunny,


I am having a small issue with this and it is only for a specific weekend and I have no idea how this is happening.

test1.JPG

For some reason on the 28th and 29th of January (which is a weeknd) is being conuted and 30/31 is not which is a mon/tues. These are the only two specific times I can find an error and I have no idea why. The only thing I know that they changed in their data is they removed holidays so that it didn't have to be taken into account in the Qlik scripting, but these days aren't holidays either so don't know why they are nulls now.

Do you have any suggestions?

sunny_talwar

I don't remember how we did this. Will try to remember once I go back home

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Hi Sunny,

First of all thanks for the response! I found the error and it was in the data and not the Qlik logic.


Thanks again for always helping and swift response!

sunny_talwar

No problem my friend