I did this using a self-join (if I understood your question correctly).
Self-join is needed so you can use Hire Date in one of the tables as your pivot, and the other table to select the employees who have been hired at the time of the pivot.
To get self-join in Tableau, you first need to add a constant dummy field in your data. I added a column called Dummy in your Excel spreadsheet and populated it with 1.
Now you can create the Tableau viz as follows:
1. Connect Tableau to the Excel file and drag Resources to the data source pane twice. Tableau will ask you what to join on - choose Dummy = (Resource 1) Dummy.
2. Go to Sheet 1, and create a calculated field as follows:
IF([Hire Date (Resources1)] <= [Hire Date] AND ([Exit Date (Resources1)] >= [Hire Date] OR ISNULL([Exit Date (Resources1)])))
THEN [P&L Cost (Resources1)]
I named it AvgPay. You see that I compare employees from the second copy (Resources1) to the hire date in the pivot. Note also how I allow for employees who haven't exited by checking for NULL.
3. Right-click on the calculated field, and choose Default Properties - Aggregation - Average.
4. Drag [Hire Date] from the first table (Resouces) to Columns - this will be your pivot. Defaults to year - right-click to choose Month. You have 2 options; the first one will ignore the year, and the second one will include the year.
5. Drag the new calculated field AvgPay to Rows.
Hope this helps. I'm attaching the packaged workbook I produced using Tableau 9.3.
If I haven't fully understood your question, and the guidance above doesn't help you solve it, don't hesitate to ask again.
PayPerMonth.twbx 194.6 KB
Thanks for the response! It looks like structurally you've done what i wanted I just don't think the numbers are correct.. As a guide, the number should be in the region of 85-95 for each month (approximately).
So a couple of questions:
1. What is the purpose of the dummy column to join the data? If we were joining the data, would we not want a unique identifier to join the data? Which leads into my second question..
2. In the data source, the AvgPay field doesn't appear to be correct. It should be bringing through the P&L Cost number, but instead it's bringing through either zero or 204.375. It doesn't look like the data has been joined correctly?
Apologies - forgot to add a filter for AvgPay. Without it, all employees are aggregated - even though the ones outside of the hire date range have their pay calculated as 0. Hence the wrong number.
To correct, drag AvgPay to the filters pane, choose "At Least", and enter 0.01. That way the employees outside the hire date range won't be considered in the calculation by definition - their pay was set to 0.
To your other question - the purpose of the dummy column is as a workaround to the cartesian product. As Tableau requires a field to join, to model a full cartesian product (which doesn't join on any field), you need to do an inner join on a dummy column.
If this still is not clear, consider the following small example: you want the cartesian product of the following small tables: table A with single column "a" and rows 1 and 2, and table B with single column "b" and rows 3 and 4. The cartesian product will be table C with 2 columns "a" and "b" and the following rows: (1, 3), (1, 4), (2, 3), (2, 4).
To achieve this in Tableau, we add column "d" (for dummy) to A and set it to a constant (1), and ditto for B. Therefore, A becomes (1, 1), (2, 1), and B becomes (3, 1), (4, 1). Now we can do an inner join on A.d = B.d and will get precisely the cartesian product described above.
Let me know if you have more questions.
That doesn't appear to work? When i add a filter for AvgPay, with an 'at least' value of 0.01, the whole graph disappears?
Oh i see... I was bringing AvgPay into the filter, as an attribute..
When i select all values and then filter on <0.01 it works.
Howcome setting it up as an attribute doesn't work?
I think attribute requires constant value for all items we are aggregating (averaging), which is not the case here.
1 of 1 people found this helpful
I have written on similar topics
It might help solving your problems.
Adding a Pivot approach mentioned above.
Please find the attached.
PS Cartesian also works, but two caveats:
-- [Hire Date] doesn't contain all dates
between MIN & MAX (it's not domain complete).
This is not a problem with MONTH aggregation, though.
-- The resulting number of records are at the 1M range.
This is not a problem with an extract in place, though.
PayPerMonth_YF.twbx 380.5 KB