Thank you for the response,
The 4 comes from there are 4 available for purchase as of April 2011.
Looking at the Introduction Date, Item_ID 12340, 12341, 12342 and 12343 were introduced 3/1/2011.
In April 2011, 12340 sold 2 units and 12341 sold 10 units. 12342 and 12343 sold nothing. So that is were I am stuck.
Since 4 are available, i want to divide the total amount sold by what is available not just the number of items that were invoiced.
Does that help clear things up?
To test with more introductions, 12344 and 12345 are introduced 9/1/2011 which would bring the total available to 6.
12346, 12347, and 12348 are introduced 1/1/2012. Finally 12349 is introduced 6/1/2012 which would bring the total available to 10 from there on out.
I cant seem to download your example, I am running version 10.1 and the file is to new for my old version.
Can you also help describe a little bit more that is going on? I am not sure why and how you changed the join between the two sheet.
Cross join is only available from version 10.2. Since you are running version 10.1, you won't be able to implement Zhouyi's solution. Another option will be to join a continuous calendar table to your data. Are you happy with this kind of approach?
I can try it but I feel like there should be some other way without including more data.
By continuous calendar, do you mean records that start at a date and continue till an end date? For example, starting 3/1/11 then going all the way to 12/31/2012?
Which data source should it link to? The Invoice Date or the Introduction date?
Thank you very much for the time and help with all of this.
Here is the result.
Your data is not well structure plus you are using older version of Tableau, so there are limited options in what you can do here. The simplest thing is to restructure your data and include a field which shows the number of available items at every invoice date.
After a second look, I noticed that continuous calendar will not work. The only option is a cross join. Since you are on version 10.1, this option looked unlikely since join calculations are only available from 10.2. However, I've used a Custom SQL approach to create a cross join equivalent in version 10.1. Cross joins generally duplicate your data. There are 10 rows in the Intro table and 77 rows in the Invoice table. Final number of rows after cross join will be 770 rows. This is because the entire Intro table is joined to every row in the Invoice table. In this way it becomes possible to determine the available items per invoice date.
I've added both tables to the same Excel file and connected that to Tableau. While connecting the data source, you will need to choose "Open with Legacy Connection" in order to make the Custom SQL functionality available. LOD expressions will be required to get the correct calculation since the data is duplicated. Please note that LOD functions are not available in Live Custom SQL data sources. The data source must be extracted to make them accessible.
Step 1: Add both tables as separate tabs in a single Excel worksheet. Connect Excel file to Tableau,choose Open with Legacy Connection and write the Custom SQL below.
Select * from [Introduction Date$], [Invoiced Date$]
Introduction Date and Invoice Date are the names of the Excel worksheets
Step 2: Extract the data source
Step 3: All the calculations are in the attached workbook. Version 10.1
Let me know if you have any questions.
Hope this helps.