Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I'm having number of documents filed (Docs) which are connected by date field and we can filter them based on Year, Month, Week and Day.
In my case I want to calculate productivity like below:
Productivity = sum(Docs)/ Total Hours
Now the issue is I don't have any hour entered by user , so I'm looking for below scenario on Total Hours
If a person has selected 1 day than Total Hours=8 , like wise for week Mon-Fri =40 , same approach to calculate Year , Month ,Week and Day.
In simple words we need to assign Total Hours some values based on selection from Calender's ,so that we can divide that number by "Doc".
Any idea how to do so???
Note: We should ignore weekends -Sat , Sun .
Regards,
Amit
Amit,
You are checking it day wise and of course you don't see those days because they are not there in your data. However, if you use the same expression along some other dimension in chart, the missing dates would be considered by networkdays(). Try it. And if you have to see them date-wise you have to create a master calendar with all dates.
May be like:
=Networkdays(Min(Date), Max(Date))*8
Hi Amit,
How about if you create a master calendar (in days) linked to your fact table, then create a field called Hours in the calendar, populate 8h for each weekday and 0 for weekends.
Then you only need Sum(Hours) and it will take the selected period without much trouble
I hope this helps,
Cheers,
Luis
Thank you this is really working , but I need little correction.
My =Networkdays(Min(Date), Max(Date))*8 should also include all dates even if data has not been entered against them.
So except Weekends , all days*8 , even if any date is missing in between.
Regards,
Amit
The networkdays() does exactly that, it doesn't count on date field. Have you tested it carefully?
Please see below:
We are missing as an example Sep-15,22,29 (Friday) dates 8 hours each.
For sample data PFA.
Regards,
AS
Amit,
You are checking it day wise and of course you don't see those days because they are not there in your data. However, if you use the same expression along some other dimension in chart, the missing dates would be considered by networkdays(). Try it. And if you have to see them date-wise you have to create a master calendar with all dates.