Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.

View solution in original post

6 Replies
tresesco
MVP
MVP

May be like:

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

luismadriz
Specialist
Specialist

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

amit_saini
Master III
Master III
Author

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

tresesco
MVP
MVP

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

amit_saini
Master III
Master III
Author

Please see below:

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

For sample data PFA.

Regards,

AS

tresesco
MVP
MVP

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.