Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
acbishop2
Creator
Creator

NetWorkDays between Min Date and Max Date, ignoring dimension

Hi All,

I have a formula to grab the total numbers of business days in the date range selected as follows:

NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))

This is close to what I want, but I need it to ignore dimensions. For example, I want to be able to calculate an employee's sales per work day. In the table below, my formula gives me only the number of days in which an employee made a sale, not the total number work days in the selection.

EmployeeSalesNetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))Sales/Work Day
Mickey Mouse522.5
Donald Duck331
Minnie Mouse824

 

I hope that makes sense. Essentially, I want to do something similar to COUNT( DISTINCT TOTAL SalesDate ) but with work days rather than calendar days.

Thanks in advance for the help.

Labels (4)
1 Solution

Accepted Solutions
Almen
Creator II
Creator II

Try the following:

NetWorkDays(Min(TOTAL [SalesDate]),Max(TOTAL [SalesDate]),$(vHolidays))

 

View solution in original post

5 Replies
Taoufiq_Zarra

@acbishop2  can you elaborate o share a sample data and the expected output ? thanks

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
acbishop2
Creator
Creator
Author

So the problem is shown in the third column of the table in my previous post.

For example, if I filter the SalesDate by Year = 2020, Week = 38, that will give me the date range of  9/13 - 9/19. In that date range, there are 5 business days. So if there are no dimensions applied, my formula NetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays)) will return the value 5. This is the value I want, and it represents the total number of workdays in the selected date range.

However, when I throw this formula into a table with the dimension of Employee, it starts returning the total number of workdays between the first day the employee made a sale and the last day the employee made a sale in the selected date range.

So, in the above example, lets say in week 38, Mickey Mouse makes 5 sales, but they were all on Monday and Tuesday. This makes it so that the formula for work days selected returns the value 2 (but I want it to be 5 still).

The desired output for this scenario is:

EmployeeSalesNetWorkDays(Min([SalesDate]),Max([SalesDate]),$(vHolidays))Sales/Work Day
Mickey Mouse551
Donald Duck350.6
Minnie Mouse851.6
Almen
Creator II
Creator II

Try the following:

NetWorkDays(Min(TOTAL [SalesDate]),Max(TOTAL [SalesDate]),$(vHolidays))

 

acbishop2
Creator
Creator
Author

That did it! Thanks so much!

shanemichelon
Partner - Creator II
Partner - Creator II

If you have Mickey Mouse, Donald Duck and Minnie Mouse all working together, you can safely just hard-code the column to zero.