Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
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.

 Employee Sales NetWorkDays(Min([SalesDate]),Max([SalesDate]),\$(vHolidays)) Sales/Work Day Mickey Mouse 5 2 2.5 Donald Duck 3 3 1 Minnie Mouse 8 2 4

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)

• ### working days

1 Solution

Accepted Solutions
Creator II

Try the following:

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

5 Replies

@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") 😉
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:

 Employee Sales NetWorkDays(Min([SalesDate]),Max([SalesDate]),\$(vHolidays)) Sales/Work Day Mickey Mouse 5 5 1 Donald Duck 3 5 0.6 Minnie Mouse 8 5 1.6
Creator II

Try the following:

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

Creator
Author

That did it! Thanks so much!

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.

Tags
Community Browser