4 Replies Latest reply: Sep 8, 2011 4:41 PM by Vladimir Komarov RSS

    Need to build a Trend Chart

    Vladimir Komarov

      Hello!

      I have a table with employment data organized the following way:

      TrendNeeded.PNG

      It shows (for each employee) how many months this person worked during the last 7 months.

      I do not have much information there except Hire and Termination Dates, but I would need to build a monthly trend chart showing how many employees actually worked during these recent months.

       

      What's the best way to do it?

      I would appreciate an advice.

       

      Best regards,

      Vladimir

        • Need to build a Trend Chart
          Stefan Wühl

          Hi Vladimir,

           

          just two thoughts:

           

          - I don't know how your expression for the months looks like, but if you sum up the rows per columns, you will get what you want, won't you?

          So why not remove all dimensions, let only the expressions (resulting in the Worked flag) remaining. Maybe use a sum() around your expression. Then you should have the aggregation over all employees, don't you?

          Convert in a bar chart etc. like you want.

           

          - You could consider using a master table and an interval match to the Hire / Termination dates. Then do a chart based on master calendar month and do a count(ID)

           

          Regards,

          Stefan

            • Re: Need to build a Trend Chart
              John Witherspoon

              I'm unclear exactly what you're after, but here's an example of tracking employees working each year based only on hire and fire dates.  I generate dates in the range using a while loop, but you can do the same with an intervalmatch like Stefan mentioned.  There are notes in my script explaining what's going on.

                • Need to build a Trend Chart
                  Vladimir Komarov

                  Thanks, John.

                  I was more concerned about building the trend from my data without linking it with Calendar.

                  I was able to do it with 'crosstable':

                   

                  MonthViewTemp:
                  crosstable (NumOfMonths, WorkedFlag, 1)
                  load
                       Employee_ID,
                       WorkedCurrMonthFlag as [0],
                       Worked1MonthsAgoFlag  as [1],
                       Worked2MonthsAgoFlag as [2],
                       Worked3MonthsAgoFlag as [3],
                       Worked4MonthsAgoFlag as [4],
                       Worked5MonthsAgoFlag as [5],
                       Worked6MonthsAgoFlag as [6]
                  Resident PSHRTemp;

                   

                  // where Worked1MonthsAgoFlag, Worked1MonthsAgoFlag, ...,Worked6MonthsAgoFlag are flags from

                  // the table above.


                  MonthView:
                  load *,
                  addmonths(monthstart($(vToday)), num#(NumOfMonths)*(-1)) as WorkMonth
                  Resident MonthViewTemp;

                   

                  As a result I got the followowing:

                  Headcount_demo.png

                   

                  Thank you for your help and the demo.

                • Need to build a Trend Chart
                  Vladimir Komarov

                  Thanks for suggestions, Stefan.

                   

                  I've found a bit different solution, using 'crosstable':

                   

                  MonthViewTemp:

                  crosstable (NumOfMonths, WorkedFlag, 1)

                  load

                   

                  Employee_ID,

                  WorkedCurrMonthFlag as [0],

                  Worked1MonthsAgoFlag as [1],

                  Worked2MonthsAgoFlag as [2],

                  Worked3MonthsAgoFlag as [3],

                  Worked4MonthsAgoFlag as [4],

                  Worked5MonthsAgoFlag as [5],

                  Worked6MonthsAgoFlag as [6]

                   

                  Resident PSHRTemp; // original table

                   

                  // where Worked1MonthsAgoFlag, Worked1MonthsAgoFlag,...,Worked6MonthsAgoFlag are

                  // flags from the table above.

                   

                  MonthView:

                  load *,

                  addmonths(monthstart($(vToday)), num#(NumOfMonths)*(-1))as WorkMonth

                  Resident MonthViewTemp;

                   

                  See the result of this approach above.

                   

                  Thank you for your comments.

                   

                  Regards,

                  Vladimri