6 Replies Latest reply: Dec 14, 2017 5:57 AM by Tresesco B

# Hours Logic Help !!!

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

• ###### Re: Hours Logic Help !!!

May be like:

=Networkdays(Min(Date), Max(Date))*8

• ###### Re: Hours Logic Help !!!

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

• ###### Re: Hours Logic Help !!!

The networkdays() does exactly that, it doesn't count on date field. Have you tested it carefully?

• ###### Re: Hours Logic Help !!!

We are missing as an example Sep-15,22,29 (Friday) dates 8 hours each.

For sample data PFA.

Regards,

AS

• ###### Re: Hours Logic Help !!!

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.

• ###### Re: Hours Logic Help !!!

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