Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
vlad_komarov
Partner - Specialist III
Partner - Specialist III

Need to build a Trend Chart

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

1 Solution

Accepted Solutions
vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author

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.

View solution in original post

4 Replies
swuehl
MVP
MVP

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
Champion III
Champion III

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
Partner - Specialist III
Partner - Specialist III
Author

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.

vlad_komarov
Partner - Specialist III
Partner - Specialist III
Author

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