Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Maybe use for networkdays calculation
NetWorkDays( MonthStart(Min(completion_sales_date)), MonthEnd(Max(completion_sales_date)), $(listofHolidays))
Maybe use for networkdays calculation
NetWorkDays( MonthStart(Min(completion_sales_date)), MonthEnd(Max(completion_sales_date)), $(listofHolidays))