david lopez Posted July 13, 2020 Share Posted July 13, 2020 Hi Community! Having trouble showing employee counts over time, attached is some sample data. My goal is to show what the headcount was going back a few months, let's say six months from todays date, in a chart (line or bar).I know the structure could be pivoted, but I'm trying not to do that as I need to do other things with the data that pivoted data won't allow. Any help with this would be greatly appreciated. Cheers! Link to comment Share on other sites More sharing options...
Fabian Duerr Posted July 14, 2020 Share Posted July 14, 2020 I can't think of a pivot operation that can achieve your goal. But if you know one, go ahead great a new table with a pivot and then link both tables (match columns). You will not lose your original table. But since you want to able to have a continuous X-axis (time) for any date (day level) I think you will have to create this axis. So make a new table that contains any day of your date range (in excel or with a data function) and then cross join your original table with the date table. You either use a data function to do that or follow this approach: https://support.tibco.com/s/article/Implementing-Cross-Join-on-two-different-tables (I know this will end up in a huge table.) Then you can add a calculated column that compares the date with your hire date and term date. So if the (date>= hire dateAND date = hire dateAND term date IS NULL) then 1 else 0. Use the CASE function for that. Now you can plot the sum of this column over time. I know this is also changing your original table, but that's the only thing I can come up with. (If you are good with data functions all these tasks can run in the back ground and you can only export the result table that contains day and head count. The input for the function would be your original table and the new datatable with continuous day axis can be created by extracting min/max date from your date columns. Then cross join both tables and summarize the join table) Looking forward to see other approaches from the community Link to comment Share on other sites More sharing options...
david lopez Posted July 14, 2020 Author Share Posted July 14, 2020 @fabd Thanks for the feedback, yah it's a tricky one, especially without adusting the original data table. And I mentioned pivoting the data, I used to do that when we were on Tableau, but that also made the data table massive and that's what I'm trying to avoid. Thanks again. Cheers! Link to comment Share on other sites More sharing options...
Fabian Duerr Posted July 16, 2020 Share Posted July 16, 2020 I have added an example file to basically the same question here: https://community.spotfire.com/questions/count-planes-scheduled-be-air-any-given-hour-day#node-1115596 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now