Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Range - 2 Date Fields - I NEED HELP!!!!!

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?

6 Replies
vvira1316
Specialist II
Specialist II

Hi,

Please find attached if that will help you.

Best Regards,

Viajy

vvira1316
Specialist II
Specialist II

Hi,

There is a small error in above example where there is no end date...

I've fixed it.

Please review the new file.

Not applicable
Author

I'm not sure how to open this file?

vvira1316
Specialist II
Specialist II

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')))

2016JunSelection.PNG

2017OctSelection.PNG

vvira1316
Specialist II
Specialist II

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;

vvira1316
Specialist II
Specialist II

Hi Lauren,

If you have found solution to your problem then can you please close this discussion.

Thanks & Regards,

Vijay