First of all, the date field in both sheets are in different format. I have converted the Revenue Date format to MMM-YY.
For the relationship, by joining Proj ID, you can get the report as you requested. However I don't see how you could incorporate Month ( I assume it is Revenue Month) into the report. What do you want to show when user select a renenue month ie. DEC-16?
PROJ ID MONTH Invoice Amount PROJ ID MONTH Revenue Amount 175117 MAR-16 1,509,082.44 175117 Mar-16 36,750,685.95 175117 NOV-14 3,772,706.10 175117 Dec-16 311,247.00 175117 DEC-13 9,431,765.25 175117 Jun-16 0 175117 NOV-13 18,863,530.50 185012 Mar-16 1,953,130.00 175117 SEP-13 3,772,706.10 185012 Dec-16 -1,953,130.00 174198 DEC-13 532,155.71 190995 Mar-16 3,115,399.57 174198 DEC-14 1,138,447.04 190995 Dec-16 58,494,309.19 174198 OCT-13 531,991.39 260451 Dec-16 2,000,000.00 185012 NOV-16 -1,953,130.00 185012 MAR-16 1,953,130.00 190995 AUG-16 -5,530,329.56 190995 DEC-15 58,494,309.19 190995 SEP-16 5,530,329.54 190995 MAR-16 3,115,399.57
Thanks Jian Wang,
to be more specific, invoicing and revenue booking are two independent events and are managed by two separate teams. In order to see how much invoicing is done vs revenue booking for a month by project is what we want to see.
For example, if the user selects Dec'16 in filter, we will need to show
1) the project ID,
2) Invoicing activity for that month (from sheet 1)
3) Revenue activity for that month (from sheet 2)
Something to show like this (below is for all months assuming that the user selected all months). Hope this helps
Can you show an example if user select Mar-16? What to be shown?
Any help please
I am relatively new to Tableau. There got to be Tableau way of blending/joining data without manipulating data in EXCEL.
For now, you can add a column called Revenue after column "Invoice" in your Invoice sheet. Add a column called "Invoice" in front of column "Revenue" in your Revenue sheet. Then do a UNION.