topic Re: Hours Logic Help !!! in QlikView

Hours Logic Help !!!

amit_saini — Thu, 14 Dec 2017 07:26:27 GMT

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 !!!

tresB — Thu, 14 Dec 2017 07:33:07 GMT

May be like:

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

Re: Hours Logic Help !!!

luismadriz — Thu, 14 Dec 2017 07:35:06 GMT

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

Re: Hours Logic Help !!!

amit_saini — Thu, 14 Dec 2017 10:27:21 GMT

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 !!!

tresB — Thu, 14 Dec 2017 10:33:57 GMT

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

Re: Hours Logic Help !!!

amit_saini — Thu, 14 Dec 2017 10:46:48 GMT

Please see below:

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

For sample data PFA.

Regards,

Re: Hours Logic Help !!!

tresB — Thu, 14 Dec 2017 10:57:55 GMT

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.