Hello,
I have a report which pulls through various team/advisor stats. I noticed that the report was pulling through advisors who had left the business, but I fixed this by adding “Employment date end”.
The issue I currently have is that it is showing some advisors in the incorrect teams (they was on that team but has since moved to another team).
I have played around with filters however I can’t seem to correct this.
Screenshot below shows date is set as previous day and i’ve highlighted the advisors who shouldn’t be showing in this team.
Any advice would be appreciated.
Hello @LParkes, I am curious. In the underlying data, when a person is moved from one team to another, does the original row of data now show an employment end date and then a new row is created for that user for the new team? Or is the original row for that user updated to replace the old team name with the new team name? Also, does the user have an ID or something that is consistent even when they change teams?
With a little more information about how the users are managed when changing teams, I can help guide you towards a solution.
Hi Dylan,
In terms of the underlying data when moving an advisor from one team to another, do you mean within Calabrio People? If so, we add a new row and update any new information with effective dates from so that we keep a trail of history of movement/changes.
Advisors all have their individual employment number aswell which will never change.
Thanks!
1 Like
Hello @LParkes, I wonder then if you create a denseRank calculation that will rank the effective dates by employment number, then filter on that rank to only return the most recent effective date, if we could ensure advisors show in the correct team.
The calculation would look something like this:
Current Team = denseRank([{Effective Date} DESC], [{employment number}], PRE_FILTER)
Now if you include this Current Team calculated field as a filter on the visuals, and set the required value to 1, then you should only see the advisor appear when you are filtering for the team they are currently on. Let me know if that helps!
1 Like
Hello @LParkes, did my previous response help resolve the issue you are facing? If so, please mark it as a solution. Otherwise, let me know what other questions you have or any errors you are experiencing, and I can help guide you further. Thank you!
