Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Everyone,
Please help. I have two date fields I am bringing in from SQL. I have an 'Employee_Hire_Date' and an 'Employee_End_Date'.
I want to be able to create a field that can identify if the employee was employed by a YEAR/MONTH.
Example:
Employee 1 -
Hire Date is 2017/01
End Date is 2017/08
I want to count if this employee was Employed in each mouth of 2017.
If I was to select the filter (of the new field I am trying to make) and select 2017/09 he would not be counted. But if I selected 2017/08 he would be counted. Any ideas?
Hi,
Please find attached if that will help you.
Best Regards,
Viajy
Hi,
There is a small error in above example where there is no end date...
I've fixed it.
Please review the new file.
I'm not sure how to open this file?
If you have a licensed version of QlikView then you should be able to open it. It is in 11.2 version...
Any way here is information
SCRIPT
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
NoConcatenate
EmpData:
LOAD [Employee ID]
// ,If(IsNull([End Date]), MonthEnd(Today()), MonthEnd(Date#([End Date],'YYYY/MM'))) as [End Date]
,MonthStart(Date#([Start Date],'YYYY/MM')) as [Hire Date]
,MonthEnd(Date#([End Date],'YYYY/MM')) as [End Date]
FROM
[..\Data\Test5.xlsx]
(ooxml, embedded labels, table is Sheet1);
tmp:
LOAD
min([Hire Date]) as MinDate,
max([End Date]) as MaxDate
RESIDENT EmpData;
LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));
Drop Table tmp;
LET NumOfDays = End - Start + 1;
Date_src:
LOAD
$(Start) + Rowno() - 1 as DateID
AUTOGENERATE $(NumOfDays);
[MasterCalendar]:
LOAD
DateID, // just in case
date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as CalDay,
week(DateID) as CalWeek,
month(DateID) as CalMonth, // simple month name; it is dual - numeric and text
dual(month(DateID) & '-' & year(DateID),
year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual
year(DateID) as CalYear,
weekday(DateID) as CalWeekday,
'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4
// dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID)) as MonthQuarter,
year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual
// and whatever else you may want here...
RESIDENT Date_src;
Drop Table Date_src;
VARIABLES
vDateSelected
MonthStart(Num(Date#(GetFieldSelections(CalMonth) & '/' & GetFieldSelections(CalYear), 'MMM/YYYY')))
EXPRESSION in CHART
=If(IsNull([End Date]),
If($(vDateSelected) >= [Hire Date],'Yes', 'No'),
If($(vDateSelected) >= [Hire Date] and
$(vDateSelected) <= [End Date], 'Yes', 'No'))
Formula for Third Text Box in Screen Shot
=MonthStart(Num(Date#(GetFieldSelections(CalMonth) & '/' & GetFieldSelections(CalYear), 'MMM/YYYY')))
Hi,
My solution was using QlikView. You are using QlikSense. It is included here.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
NoConcatenate
EmpData:
LOAD
EmployeeID
,MonthStart(Date#(StartDate,'YYYY/MM')) as HireDate
,MonthEnd(Date#(EndDate,'YYYY/MM')) as EndDate
FROM [lib://DataFile/Test5.xlsx]
(ooxml, embedded labels, table is Sheet1);
tmp:
LOAD
min([HireDate]) as MinDate,
max([EndDate]) as MaxDate
RESIDENT EmpData;
LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));
Drop Table tmp;
LET NumOfDays = End - Start + 1;
Date_src:
LOAD
$(Start) + Rowno() - 1 as DateID
AUTOGENERATE $(NumOfDays);
[MasterCalendar]:
LOAD
DateID, // just in case
date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as CalDay,
week(DateID) as CalWeek,
month(DateID) as CalMonth, // simple month name; it is dual - numeric and text
dual(month(DateID) & '-' & year(DateID),
year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual
year(DateID) as CalYear,
weekday(DateID) as CalWeekday,
'Q' & ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4
// dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID)) as MonthQuarter,
year(DateID) & ceil(month(DateID)/3) as CalQtrYear // Qn-Year, dual
// and whatever else you may want here...
RESIDENT Date_src;
Drop Table Date_src;
Hi Lauren,
If you have found solution to your problem then can you please close this discussion.
Thanks & Regards,
Vijay