Need to build a Trend Chart

[vlad_komarov]

Hello!

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

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

[swuehl]

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

[johnw]

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.

[vlad_komarov · Accepted solution]

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:

Thank you for your help and the demo.

[vlad_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