13 Replies Latest reply: Mar 20, 2014 10:24 AM by Massimo Grossi

# term sum or count

Hello to all

I am a beginner in QlikView, I try to make a qw report is based on a path of personal drive (time of entry, time out, areas, etc..).

I am looking for a formula (expression) that will allow me to count the number of times an individual is late from 7am 45

I can retrieve all day from late this expression:

=if((CalendarWeekDay = 'Monday' or  CalendarWeekDay =' Tuesday ' or CalendarWeekDay =' Wednesday ' or  CalendarWeekDay =' Thursday ' or CalendarWeekDay =' Friday')) and min((hours),1)>=maketime(7,45),min((hours),1))

 name Service Date Semaine month day Hours Arrival agent 5 INFORMATIQUE 04/02/2014 6 February Tuesday 07:56:08 agent 7 INFORMATIQUE 04/02/2014 6 February Tuesday 07:52:20 agent 1 INFORMATIQUE 03/02/2014 6 February Monday 08:37:58 agent 1 INFORMATIQUE 04/02/2014 6 February Tuesday 08:15:14 agent 1 INFORMATIQUE 05/02/2014 6 February Wednesday 08:38:14 agent 1 INFORMATIQUE 06/02/2014 6 February Thursday 08:20:36 agent 4 INFORMATIQUE 03/02/2014 6 February Monday 07:45:54 agent 4 INFORMATIQUE 07/02/2014 6 February Friday 07:50:46 agent 6 INFORMATIQUE 05/02/2014 6 February Wednesday 13:23:36 agent 6 INFORMATIQUE 07/02/2014 6 February Friday 15:38:20 agent 2 INFORMATIQUE 03/02/2014 6 February Monday 07:49:18 agent 2 INFORMATIQUE 04/02/2014 6 February Tuesday 07:49:12 agent 2 INFORMATIQUE 05/02/2014 6 February Wednesday 08:03:18 agent 2 INFORMATIQUE 06/02/2014 6 February Thursday 08:08:40 agent 2 INFORMATIQUE 07/02/2014 6 February Friday 07:58:28 agent 3 INFORMATIQUE 04/02/2014 6 February Tuesday 08:00:26

Now my problem is how can we have the sum of all minimum an agent or counting to get a result like that.

agent 1 = 4

agent 2 = 5

agent 3 = 1

agent 4 = 2

agent 5 = 1

agent 6 = 2

agent 7 = 1

• ###### Re: term sum or count

RESULT

SCRIPT

Directory;

Source:

DATE,

MONTH,

HOURS,

ZONE,

SERVICE

FROM

base.xls

(biff, embedded labels, table is Sheet1\$)

;

Table:

*,

if(match(num(WeekDay(DATE)),0,1,2,3,4) and HOURS>=maketime(7,45),1,0) as FLAGLATE,

if(NAME<>peek(NAME) or DATE<>Peek(DATE),1,0) as FLAGMINHOURS

Resident Source

order by NAME, DATE, HOURS;

DROP Table Source;

• ###### Re: term sum or count

Hello and thank you for the answer

but the problem is that I can not have the same result you I me it shows me an error loading table not found

• ###### Re: term sum or count

I supposed your source data was in base.xls

I put base.xls in the same folder as 111147.qvw

• ###### Re: term sum or count

my data is not in the xls file in a database but here is the script my script:

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

idusager,
Date(Floor("Date de Passage")) as date,
Time(Frac("Date de Passage")) as heure,
nom,
prenom,
Evenement,
idpropriete,
matricule,
libelle;

SQL SELECT *
FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

/**************calendar**************************/

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateToday = Num(Today());

TempCalendar:

\$(vDateMin) + RowNo() - 1 AS DateNumber,

Date(\$(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

MasterCalendar:

TempDate AS date,

Day(TempDate) AS CalendarDay,

WeekDay(TempDate) AS CalendarWeekDay,

Week(TempDate) AS CalendarWeek,

Month(TempDate) AS Mois,

Year(TempDate) AS Année,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateToday = Num(Today());

• ###### Re: Re: term sum or count

I changed first part of your script, before the calendar

using my logic and your tables/fields

I think you want to know if the first heure by idusager and date is before or after 7:45

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

Source:

idusager,

Date(Floor("Date de Passage")) as date,

Time(Frac("Date de Passage")) as heure,

nom,

prenom,

Evenement,

idpropriete,

matricule,

libelle;

SQL SELECT *

FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

Table:

*,

if(match(num(WeekDay(date)),0,1,2,3,4) and heure>=maketime(7,45),1,0) as FLAGLATE,

if(idusager<>peek(idusager) or date<>Peek(date),1,0) as FLAGMINHOURS          // flag first by idusager date

Resident Source

order by idusager, date, heure;

DROP Table Source;

• ###### Re: Re: term sum or count

I do not understand, I still have the same error

• ###### Re: term sum or count

try to stop execution after first load

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

Source:

idusager,

Date(Floor("Date de Passage")) as date,

Time(Frac("Date de Passage")) as heure,

nom,

prenom,

Evenement,

idpropriete,

matricule,

libelle;

SQL SELECT *

FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

exit script;

is there  a table called Source in Table Viewer?

• ###### Re: term sum or count

no error

• ###### Re: term sum or count

ok ca no longer generates an error, but it shows me

• ###### Re: term sum or count
if I create a simple table with user id how I will use the term count
count ({\$ <FLAGLATE = {1}} FLAGMINHOURS)
• ###### Re: term sum or count

add a tablebox using Table and verify if the records you wants are flagged

if yes build the chart using the expression in my attachment (modified because of different source data);

dimension     idusager

expression     count({\$ <FLAGLATE={1},FLAGMINHOURS={1}>} DISTINCT date)

• ###### Re: term sum or count

thank you it works well

• ###### Re: term sum or count

Please close this discussion by giving Correct and Helpful answers to the posts which are useful for you.  It helps others in finding answers for similar scenarios.