topic Re: How to get correct employee count datewise in QlikView

How to get correct employee count datewise

Mon, 23 Nov 2015 03:23:08 GMT

Hi All

PFA

here by using below script

Data:

LOAD *,Date(Floor(TimeData)) as Date,

Time(Frac(TimeData)) as Time ;

LOAD id,

[Employee id],

EventID,

TimeData

FROM

(ooxml, embedded labels, table is Sheet1);

and in text object expression count(employee id) it is giving 30

i want count as 3 here only 3 dates are available

how to get this please reply me

Regards

Hemesh

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 03:26:50 GMT

Hi,

Try count(Distinct [employee id])

Regards,

jagan.

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 03:30:59 GMT

Try this expression

=Sum(Aggr(Count(Distinct [employee id]), Date)

Hope this helps you.

Regards,

Jagan.

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 03:36:13 GMT

i am going to use count(employee id ) in many situations so is their any way to make changes in background

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 03:41:51 GMT

By using this i am not getting the correct results

=Sum(Aggr(Count(Distinct [employee id]), Date))

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 03:45:43 GMT

i have to get count as 3 i.e. here 3 dates are available

only one employee for one date

by removing floor it will show count as 30

Re: How to get correct employee count datewise

HirisH_V7 — Mon, 23 Nov 2015 03:47:58 GMT

Hi,

You can get the count of date with respect to emp.id Like this,

=Aggr(Count(Distinct Date),[Employee id])

PFA.

Hope this helps,

Regards,

Hirish

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 03:48:53 GMT

also have a look on the post by me with name How to get correct total

for this also i didn't received answer

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 03:49:45 GMT

Hi,

If you are not using time remove that column and use Distinct in the load statement like below.

Data:

LOAD DISTINCT

id,

[Employee id],

EventID,

Date(Floor(TimeData)) as Date;

LOAD id,

[Employee id],

EventID,

TimeData

FROM

(ooxml, embedded labels, table is Sheet1);

Hope this helps you.

Regards,

Jagan.

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 03:54:53 GMT

i want to use time column in coming scripts

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 04:04:44 GMT

Hi,

Then there is no way, you must use

=Sum(Aggr(Count(Distinct [employee id]), Date)

Not sure why you are not going with the above expression?

Regards,

Jagan.

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 04:08:52 GMT

by using =Sum(Aggr(Count(Distinct [employee id]), Date)) i am getting count as 0

Re: How to get correct employee count datewise

rajuamet — Mon, 23 Nov 2015 04:15:21 GMT

Hi,

Try this in back end.

Data:

LOAD *,Date(Floor(TimeData)) as Date,

[Employee id]&'-'&Date(Floor(TimeData))as Employee_id,

Time(Frac(TimeData)) as Time ;

LOAD id,

[Employee id],

EventID,

TimeData

FROM

(ooxml, embedded labels, table is Sheet1);

By this you are creating Employee id & Date combo.

So, in front end use Count(Distinct Employee_id). You will get 3.

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 04:17:15 GMT

Can you attach sample qlikview file?

Regards,

Jagan.

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 04:29:57 GMT

for the script

Data:

LOAD id,

[Employee id],

EventID,

TimeData

FROM

(ooxml, embedded labels, table is Sheet1);

Emp2:

LOAD [Employee id], Date,

Time(Min(Time),'hh:mm:ss TT') as TimeIn,

Time(Max(Time),'hh:mm:ss TT') as TimeOut

Resident Data Group By [Employee id], Date;

DROP Field TimeData;

i am getting count(employee id ) as 3 and for whole script i am getting

count(employee id) as 1

Re: How to get correct employee count datewise

jagan — Mon, 23 Nov 2015 04:44:08 GMT

Hi,

If you want by date in text object then you have to use the Aggr() like below

=Sum(Aggr(Count(DISTINCT [Employee id]), [Employee id],Date))

Hope this helps you.

Regards,

jagan.

Re: How to get correct employee count datewise

nagarjuna_kotha — Mon, 23 Nov 2015 05:59:54 GMT

Hi Reddy,

Here I worked around your requirement.Now count giving as u expected .

Check in Script and Expression.

PFA...

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 06:03:03 GMT

i don't have license will you please send script and what ever require in layouts

Re: How to get correct employee count datewise

nagarjuna_kotha — Mon, 23 Nov 2015 06:13:39 GMT

Okay ,Well

Script :

Data:

LOAD id,

[Employee id],

EventID,

TimeData,

Date( TimeData) as Date,

Date#( Date( TimeData),'DD/MM/YYYYY') as NewDate

FROM

(ooxml, embedded labels, table is Sheet1);

Expression:

=aggr(Count(DISTINCT NewDate), [Employee id]).

Let me know working or not.

Re: How to get correct employee count datewise

Mon, 23 Nov 2015 06:22:24 GMT

working but raghu r answer will working good check correct answer