Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try this.
Sum(Aggr(NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today()),rep_name))
Try this.
Sum(Aggr(NetWorkDays (FirstSortedValue({1<year = {'2020'}, rep_name=p(rep_name)>} distinct date, date), today()),rep_name))
It worked perfectly.
Thanks a lot!