topic Re: Calculating days from date in QlikView

Calculating days from date

Wed, 03 Feb 2016 13:48:02 GMT

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.

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 13:51:05 GMT

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)

Re: Calculating days from date

Wed, 03 Feb 2016 13:54:29 GMT

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,

Re: Calculating days from date

Wed, 03 Feb 2016 13:55:31 GMT

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)

Re: Calculating days from date

Wed, 03 Feb 2016 14:08:25 GMT

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.

Re: Calculating days from date

Wed, 03 Feb 2016 14:12:06 GMT

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.

Re: Calculating days from date

Wed, 03 Feb 2016 15:12:12 GMT

Hi all

thanks for the reply.

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

Please help me how to correct this.

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 15:16:16 GMT

Please post some sample data and your required results.

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

Re: Calculating days from date

Wed, 03 Feb 2016 15:16:28 GMT

Please provide sample data and expected result.

Re: Calculating days from date

Colin-Albert — Wed, 03 Feb 2016 15:17:23 GMT

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.

Re: Calculating days from date

Wed, 03 Feb 2016 16:28:19 GMT

Hi Experts,

i have attached a sample

Please help

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 16:31:43 GMT

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

Re: Calculating days from date

Wed, 03 Feb 2016 16:37:43 GMT

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

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 16:42:48 GMT

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

Re: Calculating days from date

Wed, 03 Feb 2016 16:50:30 GMT

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

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 16:55:30 GMT

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.

Re: Calculating days from date

Wed, 03 Feb 2016 17:15:58 GMT

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?

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 17:34:47 GMT

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.

Re: Calculating days from date

swuehl — Wed, 03 Feb 2016 17:42:41 GMT

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

Re: Calculating days from date

Wed, 03 Feb 2016 17:59:52 GMT

thank you so much swuehl