Count Employees entry where date does not exist(except weekends)

[Alarkis]

Hey all,

I need to know the  absences of employees from the this table

I want to know the missed days of employee where a date is missing 

Except for weekends..

thanks..

Data similar to this:

EMPNO	Date	Status
810	3/3/2020	1
805	4/3/2020	1
97	5/3/2020	1
810	8/3/2020	1
356	9/3/2020	1
810	10/3/2020	1
190	11/2/2020	1
849	12/2/2020	1

[Lisa_P]

This will not work if the dates don't exist. You can create an additional table with the list of all dates between the min and max as per a master calendar, also generate the Weekday function to ignore Saturday and Sunday.

eg 

MasterCalendar:
LOAD
    Date(Date) AS Date,
    Year(Date) AS Year,
    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,    
    Month(InvoiceDate) As Month,
    Day(InvoiceDate) As Day,
    Week(InvoiceDate) As Week,
     Weekday(Date) as WeekDay;

Load Date(MinDate + IterNo() -1 ) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load
    Min(Date) AS MinDate,
    Max(Date) AS MaxDate
RESIDENT Employees;

This script works bottum up to create first Min and Max Dates, then a list of dates with no gaps, then creates date dimensions including Weekday.

Create a table with EmpNo as dimension and the expression on the front end would then be:
Count({<Status=-{1}, Weekday=-{'Sat.', 'Sun.'}>}Status)// Count all dates where Status not equal to 1, not on Saturday or Sunday

[Alarkis]

Thanks for your reply i tried with my current Master Calender i dont think it makes generations:

Iam already using for other tables:

is there a way to modify current master calendar or is there a way to do it without master calendar using another way?

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);


Let varMinDate = num(MakeDate(2014,01,01));
Let varMaxDate = num(Today());

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
num(TempDate) AS %Date,
Date(TempDate) as Date ,
Year(TempDate) As Year,
Year(TempDate) As سنة,
Month(TempDate) As Month,
Month(TempDate) As شهر,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Load * Inline [
Quarter, "ربع سنوي"
Q1, "الربع الأول "
Q2, "الربع الثاني "
Q3, "الربع الثالث "
Q4, "الربع الرابع "
];

[Lisa_P]

Do you have multiple dates in your data ?

Maybe this date is not the date your calendar is based on.

If this is the only thing you need this date for then you can keep a separate calendar for this and only deriving the weekday and giving it a different name.

EmpCalendar:
LOAD
    Date(EmpDate) AS EmpDate,
        Weekday(EmpDate) as EmpWeekDay;

Load Date(MinEmpDate + IterNo() -1 ) AS Date While (MinEmpDate + IterNo() - 1) <= Num(MaxEmpDate);

Load
    Min(EmpDate) AS MinEmpDate,
    Max(EmpDate) AS MaxEmpDate
RESIDENT Employees;

Count({<Status=-{1}, EmpWeekDay=-{'Sat.', 'Sun.'}>}Status)// Count all dates where Status not equal to 1, not on Saturday or Sunday

[Alarkis]

Hey,

I am receiving this Error:

Script:

-------------

[EVENTS]:
LOAD NO,
USERNO,
DT,
CODE,
TYPE,
EVENTID
Where EVENTID = '66';

SELECT "NO",
"USERNO",
"DT",
"CODE",
"TYPE",
"EVENTID"
FROM "TSSPICA"."EVENTS";

-----------------------------------------

EmpCalendar:
LOAD
Date(DT) AS EmpDate,
Weekday(DT) as EmpWeekDay;

Load Date(MinEmpDate + IterNo() -1 ) AS Date While (MinEmpDate + IterNo() - 1) <= Num(MaxEmpDate);

Load
Min(DT) AS MinEmpDate,
Max(DT) AS MaxEmpDate
RESIDENT [EVENTS];

The following error occurred:

Field 'DT' not found

The error occurred here:

?