Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rm1
Contributor III
Contributor III

Calculate total working days based on selected sales representives

Hello Everyone!
I need to calculate the total working days of a group of sales reps.
Everyone has the same calendar, but different starting dates (someone started to work after the 1st of January).

In order to calculate working days for a single person that I chose inside my filters, I simply use the following formula:
NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today())

This works perfectly for a single rep because it counts since its first working day until today.
My question is: How can I calculate the exact amount of days if I choose more than one rep?

Using:
NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today()) * (Count(distinct rep_name))
is wrong because the formula picks the rep with maximum number of days and multiplies them for the number of reps that I choose.
E.G. REP 1 = 30 days; REP 2 = 100 days
this formula returns 200 days instead of 130.

Can anyone help me?
Thanks a lot in advance

All the best!

Labels (2)
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this.

Sum(Aggr(NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today()),rep_name))

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this.

Sum(Aggr(NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today()),rep_name))

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
rm1
Contributor III
Contributor III
Author

It worked perfectly.

Thanks a lot!