Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Tags (2)
6 Replies
vvira1316
Valued Contributor II

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

Hi,

Please find attached if that will help you.

Best Regards,

Viajy

vvira1316
Valued Contributor II

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

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

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

I'm not sure how to open this file?

vvira1316
Valued Contributor II

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

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
Valued Contributor II

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

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
Valued Contributor II

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

Hi Lauren,

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

Thanks & Regards,

Vijay