Calculating days from date

[#1]

Hi Experts,

i have a table with following fields.

Table1:

Load EmployeeName,

        Date,

        TransactionID

from Table1.qvd(qvd);

in a pivot table, i need to calculate the no. of days worked by an Employee in a month.

condition for calculation is,

1. if there is a transaction against an employee on a particular date, then it mean that employee has worked on that day.

2. if the weekday(Date) is Saturday, then count is 0.5 else 1

How can i do this in expression.

Please help me with the expression.

[swuehl]

Set DateFormat = 'MM/DD/YYYY'; // Or whatever your date format in Date is

Table1:

LOAD *, If( WeekDay(Date)=5, 0.5,1 ) as WorkDayCount;

Load EmployeeName,

        Date,

        TransactionID

from Table1.qvd(qvd);

Then create chart with dimension EmployeeName and as expression

=Sum(WorkDayCount)

[#3]

Something like this maybe help you

Table1:

LOAD *,

          IF(weekday(Date) = 'Sat',0.5,1) AS COUNT_WORKING;

Load EmployeeName,

        Date,

        TransactionID

from Table1.qvd(qvd);

Then, in Pivot Table you show SUM(COUNT_WORKING) by Employe

Hope it helps

Regards,

[#4]

Something Like this  ..

Table1:

LOAD *,if(not ISNULL(TransactionID)  or num(TransactionID)>0, If( WeekDay(Date)=5, 0.5,1 ),0) as WorkDayCount;

Load EmployeeName,

        Date,

        TransactionID

from Table1.qvd(qvd);

Then create chart with dimension EmployeeName and as expression

=Sum(WorkDayCount)

[#5]

Try like below UI Expression:

Sum(IF(Workday(Date_Field)=5, 0.5,1))

I would suggest add the flag in the script instead of UI if cond expression.

[#6]

Hi Rajendran,

Please use the below condition.

Load EmployeeName,

        Date,

        TransactionID,

      if(isnull(value)=0 and WeekDay(value)<>5, 1, if(isnull(value) and  WeekDay(value)=5,0.5,0)) as weektotal

from Table1.qvd(qvd);

and in the chart use

=sum(weektotal)

Thanks,
Sreeman.

[#7]

Hi all

thanks for the reply.

i tried all the three, but its not giving required value.

Please help me how to correct this.

[swuehl]

Please post some sample data and your required results.

It's hard to create a correct solution with the information provided.

[#9]

Please provide sample data and expected result.

[Colin-Albert]

It depends on which days of the week are working days.

If the working week is Monday-Friday then look at the NETWORKDAYS() function as this calculates the number of working days between two dates and you can add a list of holiday dates to be excluded.

If the working week includes Saturday or Sunday then you will need to create your own expression.