Expression Issues

Tue, 27 Sep 2011 06:50:18 GMT

Hi All,

I am facing one issue that is i am having a scenario in which i have to calculate the number of working days and standars hours. I am attaching the XL file in which there are basically 6 fields i want to calculate number of working days and then standard hours correspondingly.

Scenario is : Suppose there is an employee whose emp_number is 1101 whose Assignment(Asssdate) starts on 1-08-2011 and ends(Asseddate) on 15-09-2011. So my requirement is to calculate number of working days and then standard hours basis on this date difference excluding saturday and sunday. I am using NetWorkDays() function for that but problem is that this report is prepare at the end of each month so if:

StartDate	EndDate	EmpNo	EmpName	Location	Position
1-Aug-2011	15-Sep-2011	11001	SSSS	Delhi	Assistant Manager
16-Sep-2011	To present	11001	SSSS	Mumbai	Manager

My start date is 1 aug 2011 and end date is 15 sep 2011 in between there are near about 33 working days excluding saturday,sunday. So if i want to see the report for aug month only then it will show the working days upto 30 aug 2011. And when i want to see the report for September month then it will show the results from 1-sep-2011 to 31-sep-2011 i.e half results are coming from the previous assignment and half from the present assignment. This particular employee is presently working as a Manager in Mumbai.

How i am calculating is :

I am making one variable named as MaxAssdate = IF( ISNULL(YearType),MAX(Asssddate), DATE(NUM(30) &'/' &MAX(CalendarMonthNum) & '/' & MAX(NUM(left(CalendarNumYearMonth,4))),'DD/MM/YYYY'))

For Working Days =

NetWorkDays(

(IF(Asssddate >= DATE(MONTHSTART(MaxAssdate),'DD/MM/YYYY'),DATE(Asssddate,'DD/MM/YYYY'),DATE(MONTHSTART(MaxAssdate),'DD/MM/YYYY'))),

(IF(Asseddate <= DATE(MonthEnd(MaxAssdate),'DD/MM/YYYY'),DATE(Asseddate,'DD/MM/YYYY'),IF(MONTH(DATE(MONTHEND(MaxAssdate),'DD/MM/YYYY'))=MONTH(TODAY()),

DATE(TODAY(),'DD/MM/YYYY'),DATE(MONTHEND(MaxAssdate),'DD/MM/YYYY'))))

)

For Standard Hours:

I am making one variable sd = Count(DISTINCT [EmpployeeNo&EmployeeName])

Standard Hours :

if(

if(sd > 1, [Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)),

if(NOOFROWS()>1,([Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))/TOTDAYS,

[Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))) > 184, 0,

if(sd > 1, [Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)),

if(NOOFROWS()>1,([Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))/TOTDAYS,

[Working Days] * (count(DISTINCT [EmpployeeNo&EmployeeName])*IF(LEFT(SU_Jobname,16)='Staff Accountant',7,8)))))

There is a logic behind calculating standard hours i.e if SU_Jobname = 'Staff Accountant ' then take 7 hours as a working hours in a day otherwise taking 8 hours. But this logic is not working fine. Please let me know any other simple way to calculate this asp.

I am attaching the XL file for your reference.

Regards,

Saurabh Mudgil

Re: Expression Issues

lironbaram — Tue, 27 Sep 2011 10:06:26 GMT

hei

attach is an example

it uses the interval match function

hope it helps you

Re: Expression Issues

Thu, 29 Sep 2011 09:01:00 GMT

Thanks liron for ur reply. This solution helps me in getting the correct results.

Regards,

Saurabh Mudgil

topic Re: Expression Issues in QlikView

Expression Issues

Re: Expression Issues

Re: Expression Issues