formula/script help

[Anonymous]

Myself a qlik view beginner.

I have Calendar with dates generated from 1/1/2015 to 12/31/2015,

I use the below mentioned formula to calculate productivity for the team and individual users.

formula:

(Sum(sales)*3)/(networkdays(min(completion_sales_date),max(completion_sales_date),$listofHolidays))*8.

This works fine when applied for the whole team on month/quarter/year basis.

USER	SALES	PRODUCT ID	completion_sales_date
A	40	A0001	2/2/2015
B	23	A0002	3/2/2015
C	18	B0003	3/2/2015
D	21	A0001	4/2/2015
A	15	A0002	3/15/2015
B	12	B0003	2/27/2015
C	33	A0002	3/16/2015

Problem Area: When we break it down to individual’s…..

Here user A has only one entry in the table for the month of February, i.e., 2/2/2015. Therefore the formula would take min and max date as  2/2/2015 i.e only 8 hours for the whole month.

But ideally, it should be 19 working days (or 19 multiplied by 8, 152 hours(excluding weekends,holidays)).

For the month of February for USER A, calculation on the chart should show

40*3(STANDARD VALUE)/152 hours =0.78 or 78%

Thanks in advance.

[swuehl · Accepted solution]

Maybe use for networkdays calculation

NetWorkDays( MonthStart(Min(completion_sales_date)), MonthEnd(Max(completion_sales_date)), $(listofHolidays))