How to Specify Year in Data Load Editor

[ssssssss88888888]

Hello, I have the below code in my data load editor. What we are trying to do is see which employees produce 4 or more "Incident IDs" per year. The below code establishes this condition being met as "Completed", and then a second pass is done to associate each employee and their status (indicated as "Ultimate"). this is helpful because then we can divide the total number of "Ultimate" status by the total number of employees, and then we can get a percentage completion.

one more thing i want to do is to get the data load editor to specify that this condition has to be met within the same calendar year. so if i model the data now, some people are indicated as "Ultimate" because between their 2021 and 2022 Incident IDs, they have more that 4. I want to specify so that you have to have 4 within the same calendar year. There is a master calendar created, which I can share if it would be helpful in figuring this out. thank you!

AGGR:

Load "Reported by", If(Count("Incident ID") >=4, 'Yes', 'No') as Completed

Resident [Record Extract] group by "Reported by";

Inner Join([Record Extract]) Load "Reported by", Completed resident AGGR;

drop Table AGGR;

AGGR2:

Load

Distinct "Reported by",

if (Completed = 'Yes', "Reported by") as Ultimate

Resident [Record Extract];

[Rockstar7]

@ssssssss88888888  Include year condition in your calculation like below , you will get calender year ultimates

if ( year = max(year) and Completed = 'Yes', "Reported by") as Ultimate.

or

you can create a variable with year like    vYear= year(today()) then use the variable in the expression

[ssssssss88888888]

thank you!! for whatever reason its telling me "Field 'Year' not found', even though this is set earlier in the code. here is the entirety of the code, do you know why that is?

[Record Extract]:
LOAD
"Incident ID",
Site,
Date("Reported Date", 'MM-DD-YY') as "MasterDate",
Classification,
"Observation Type",
"Event Location",
"Specific Area",
" ",
"Reported by",
Department
FROM [lib://AttachedFiles/Hazard Recognition Extract (1).xlsx]
(ooxml, embedded labels, table is [Hazard Recognition Extract]);

[Min_Max]:

Load

Min("MasterDate") as MinDate,

Max("MasterDate") as MaxDate

Resident [Record Extract];

Let vMinDate = Peek('MinDate',0,'Min_Max');

Let vMaxDate = Peek('MaxDate',0,'Min_Max');

Master_Calendar:

Load *,

MonthName("MasterDate") as MonthName,

QuarterName("MasterDate") as QuarterName,

YearName("MasterDate") as YearName,

'Q'&Ceil(Month("MasterDate")/3) as Quarter,

Year("MasterDate") as Year,

Month("MasterDate") as Month,


WeekDay("MasterDate") as WeekDay,

Date(monthstart("MasterDate"),'MMM YY') as MonthYear;

Load

Date($(vMinDate) + IterNo() - 1) as "MasterDate"

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

AGGR:

Load "Reported by", If(Count("Incident ID") >=4, 'Yes', 'No') as Completed

Resident [Record Extract] group by "Reported by";

Inner Join([Record Extract]) Load "Reported by", Completed resident AGGR;

drop Table AGGR;

AGGR2:

Load

Distinct "Reported by",

if (Year = Max(Year) and Completed = 'Yes', "Reported by") as Ultimate

Resident [Record Extract];

[Rockstar7]

"Field 'Year' not found', this error is due to year field is missing from the  Record Extract table. So using year function, can derive the year and can use it in the calculation 

AGGR2:

Load

Distinct "Reported by",

if (Year = Max(year("Reported Date")) and Completed = 'Yes', "Reported by") as Ultimate

Resident [Record Extract]