Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to implement a year-over-year comparison of employee retention and turnover. My first task was going to see who was employed during an entire calendar year just to get the bar chart to display properly before I start customizing. I'm unable to get my bar chart to display any information, however. What I did:
Data has Employee ID, Location, Start Date (date, day, month, year, etc), End Date (date, day, month, year, etc)
vCensus1Start and vCensus1End are both 2015
Start Date/End Date data is sound and there will be no End Date prior to a Start Date. There are employees who have been employed multiple years. My issue does not lie here.
Dimension: Location
Expression: =if((END_YEAR)>'$(vCensus1End)'and(START_YEAR)<'$(vCensus1Start)',count(distinct(EMP_ID)),0)
As a side note for those of you noticing - I don't care yet about people starting on 1/1 or terminating on 12/31 - I'll account for this once I get the chart to work.
I'm not sure why this won't display. Oddly enough, I WILL get a result if I include START_YEAR and END_YEAR into my dimensions, but it displays as a stacked bar with one color being the totals for END_YEAR 2016 and the other color for our "current" records with 9999 as a default year. It's displaying all criteria in one bar in different colors if the END_YEAR differs, which is not useful.
What I want is my Location as a dimension.
One color bar to display my count of unique employees who have been employed for all of 2015.
One color bar to display my count of unique employees who have been employed for all of 2016.
What am I doing wrong?
Try
=Count(DISTINCT if((END_YEAR)>'$(vCensus1End)'and(START_YEAR)<'$(vCensus1Start)', EMP_ID))
edit:
Try
=Count(DISTINCT if((END_YEAR)>'$(vCensus1End)'and(START_YEAR)<'$(vCensus1Start)', EMP_ID))
edit:
Thank you! This works perfectly!