6 Replies Latest reply: Sep 18, 2017 1:14 PM by Vijay Vira RSS

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

    Lauren Mills

      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?

        • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
          Vijay Vira

          Hi,

           

          Please find attached if that will help you.

           

          Best Regards,

          Viajy

          • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
            Vijay Vira

            Hi,

             

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

             

            I've fixed it.

             

            Please review the new file.

              • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
                Lauren Mills

                I'm not sure how to open this file?

                  • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
                    Vijay Vira

                    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

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                    • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
                      Vijay Vira

                      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;

                  • Re: Date Range - 2 Date Fields - I NEED HELP!!!!!
                    Vijay Vira

                    Hi Lauren,

                     

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

                     

                    Thanks & Regards,

                    Vijay