Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculating days from date

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.

View solution in original post

19 Replies
MVP
MVP

Re: Calculating days from date

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)

Not applicable

Re: Calculating days from date

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,

Not applicable

Re: Calculating days from date

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)

Not applicable

Re: Calculating days from date

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.

Not applicable

Re: Calculating days from date

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.

Not applicable

Re: Calculating days from date

Hi all

thanks for the reply.

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

Please help me how to correct this.

MVP
MVP

Re: Calculating days from date

Please post some sample data and your required results.

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

Not applicable

Re: Calculating days from date

Please provide sample data and expected result.

Re: Calculating days from date

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.