How to get correct employee count datewise

[#1]

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

[jagan]

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.

[#3]

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

[raju_insights · Accepted solution]

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.

[jagan]

Can you attach sample qlikview file?

Regards,

Jagan.

[#6]

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

[jagan]

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.

[nagarjuna_kotha]

Hi Reddy,

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

Check in Script and Expression.

PFA...

[#9]

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

[nagarjuna_kotha]

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.

[#11]

working but raghu r answer will working good check correct answer