2 Replies Latest reply: Mar 21, 2012 5:32 PM by Marc Livingston RSS

    Help with merge the dates

      Hi

      I have the following scenario

       

      Table1:LOAD * INLINE[ID,Start_Date, End_Date, City
      01,1/1/2010,1/5/2011,001
      02,1/4/2010,1/12/9999,002
      03,1/2/2010,1/12/9999,002
      04,1/5/2010,1/12/9999,004
      05,1/2/2010,1/5/2011,001
      06,1/5/2010,1/2/2011,002
      07,1/7/2010,1/3/2011,003
      08,1/8/2010,1/3/2011,001
      09,1/3/2010,1/5/2011,003
      10,1/10/2010,1/5/2011,004

      11,1/7/2010,1/3/2011,001
      12,1/10/2010,1/3/2011,002
      13,1/3/2010,1/5/2011,003

      14,1/8/2010,1/2/2011,003

      15,1/8/2010,1/3/2011,003];

       

      I want to create a Chart that show me how many are active and how many are inactive by month or year,


      For example I select a Month and the Chart show me tow bars

       

      5-       __

      3-     |    |    ___

      1-     |    |    |    |

      ____|__|__|__|____

                (a)         (b)      Month

      (a) Active (b) Inactive

       

      My problem is in the dimension; I need merge the dates or do something so I can only see the Active and Inactive by month or year

       

      I will appreciate you help

      thanks

        • Re: Help with merge the dates
          Brandon Apperson

          If I am understanding your problem correctly then I believe the following should help:

           

          In order to select a month and year I created another inline table:

           

          I used your first table and then added to the script

           

          Dates:

          LOAD * INLINE

          [

          MONTH, YEAR

          1, 2010

          1, 2011

          2, 2010

          2, 2011

          3, 2010

          3, 2011

          4, 2010

          4, 2011

          5, 2010

          5, 2011

          6, 2010

          6, 2011

          7, 2010

          7, 2011

          8, 2010

          8, 2011

          9, 2010

          9, 2011

          10, 2010

          10, 2011

          11, 2010

          11, 2011

          12, 2010

          12, 2011

          ]

           

          If you wanted to add more years then you could always triple up on each e.g.

          12, 2010

          12, 2011

          12, 2012

           

          Then reload the script.

           

          Bring in the MONTH and YEAR fields

           

          Now define a couple of variables:

           

          Settings->Variable Overview...->Add

           

          Make the variables

          getMonth = GetFieldSelections(MONTH)

          getYear = GetFieldSelections(YEAR)

          newDate = MakeDate(getYear, getMonth)

           

          Now create a new chart.

          Add a calculated dimension with the following formula:

          =IF( newDate >= Start_Date and newDate <= End_Date,'Active','Inactive')

           

          Then define the expression as = COUNT(ID)

           

          Now you will have a chart that, when a month and year are selected will show what I think you are looking for from the question.

           

          Hope this helps!

           

          I also have attached a sample qvw if you need extra reference.

          • Re: Help with merge the dates

            Also similar to the above is create a calendar table.

             

            Use an expression similar to this:

             

            sum(aggr((if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <=if(Headstatus='A',monthend([Active Date]),MonthEnd([Inactive Date])),1,0)),CalendarMonthEnd,Employee ))//)

             

            Active date i defined as if employee is active take today() * 2

             

            Then you can create a chart by month and year and see who was active each month.