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.
Hi Experts,
i have attached a sample
Please help
And what is your required result? Where do you see incorrect results?
Hi
these are the requirements
1. for Saturday, count should be 0.5
2. if there is null in transaction id, then that date shouldnt be counted
Maybe just change the number format on number tab for expression "Days Worked" from integer to a format with decimals?
swuehl,
after changing to decimal, it worked now for this sample which has one month (2 days)
but in my report, i have 6 months data and i apply this same condition, i m not getting correct values.
Please give suggestions for that
I don't see any reason why it shouldn't work with more than 1 month data.
Besides this, if you don't tell us what you see and what you expect (or even better, update your sample to show the issue), it's almost impossible to give any advice. I am no clairvoyant.
swuehl,
again i have attached correct sample from my original report for a week in jan.
the count for each empcode is giving wrong values like 56, 60 which is not possible.
can u please correct me to get required values?
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.
And another version with the mentioned additional table and the simple sum() expression.
thank you so much swuehl