7 Replies Latest reply on May 16, 2018 6:21 PM by Okechukwu Ossai

    Total Items Available needed for Quantity per Item Calculation

    Kevin Harkins

      Hello,

       

      I am having trouble finding the average quantity per item when linking two different data sources. Attached are the sources and the workbook for an example.

      What I would like to ultimately get to is show at any month, the quantity per item with all of the items that were available during that time. For example, in the "Average Quantity per Item" worksheet, under the 2011 April column, 4 items are available but only two sold totaling 10 units that month. The average should be 2.5 (10 units/ 4 items) but I am getting 5 (10 units/ 2 items). How do I get the 2.5 or am i looking at this correctly?

       

      Thanks in advance,

        • 1. Re: Total Items Available needed for Quantity per Item Calculation
          Okechukwu Ossai

          I can't match your explanation to the data provided. In the invoice month of April 2011. There are only 2 item IDs. 10 units were sold. 2 units from Item_ID 12340 and 8 units from Item_ID 12341.

           

          Where did you get the 4 items from?

           

          • 2. Re: Total Items Available needed for Quantity per Item Calculation
            Zhouyi Zhang

            Hi, Kevin

             

            Please find my solution attached, below is the screenshot of steps and results.

             

            Hope this helps

             

            ZZ

            • 3. Re: Total Items Available needed for Quantity per Item Calculation
              Kevin Harkins

              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.

               

              Thanks,

              • 4. Re: Total Items Available needed for Quantity per Item Calculation
                Kevin Harkins

                Hi Zhouyi,

                 

                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.

                 

                Thanks,

                • 5. Re: Total Items Available needed for Quantity per Item Calculation
                  Okechukwu Ossai

                  Hi Kevin,

                   

                  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?

                   

                  Ossai

                  • 6. Re: Total Items Available needed for Quantity per Item Calculation
                    Kevin Harkins

                    Hi Ossai,

                     

                    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.

                    • 7. Re: Total Items Available needed for Quantity per Item Calculation
                      Okechukwu Ossai

                      Hi Kevin,

                       

                      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.

                      Ossai