Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating days from date

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.

19 Replies
Not applicable
Author

Hi Experts,

i have attached a sample

Please help

swuehl
MVP
MVP

And what is your required result? Where do you see incorrect results?

Not applicable
Author

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

swuehl
MVP
MVP

Maybe just change the number format on number tab for expression "Days Worked" from integer to a format with decimals?

Not applicable
Author

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

swuehl
MVP
MVP

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.

Not applicable
Author

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?

swuehl
MVP
MVP

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.

swuehl
MVP
MVP

And another version with the mentioned additional table and the simple sum() expression.

Not applicable
Author

thank you so much swuehl