Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor 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
MVP
MVP

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.

6 Replies
MVP
MVP

Re: Hours Logic Help !!!

May be like:

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

luismadriz
Valued Contributor

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

amit_saini
Honored Contributor III

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

MVP
MVP

Re: Hours Logic Help !!!

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

amit_saini
Honored Contributor III

Re: Hours Logic Help !!!

Please see below:

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

For sample data PFA.

Regards,

AS

MVP
MVP

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.