Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
If there are multiple transactions per date, you need to consider & remove the duplicates per employee and date.
You can do it like this:
=Sum(Aggr(WorkDayCount, emp.code, PRINTED_DATE))
Or you would need to create a table in the data model with one record per employee and date and the WorkdayCount value.
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)
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,
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)
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.
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.
Hi all
thanks for the reply.
i tried all the three, but its not giving required value.
Please help me how to correct this.
Please post some sample data and your required results.
It's hard to create a correct solution with the information provided.
Please provide sample data and expected result.
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.