Counts over time

[david lopez]

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!

[Fabian Duerr]

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

[david lopez]

@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!

[Fabian Duerr]

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