3 Replies Latest reply on Nov 4, 2018 10:09 AM by Jim Dehner

    Extract design to enable filtering correctly

    Chris Harper

      Hi there, have been running up against a wall on how to corrrectly extract our data to enable filtering the way we want. Here is our scenario:

       

      We have a table of all widgets, including a widget received date column.

       

      We then left join to a transactions table on a widget ID column. Not all widgets have a transaction. The transaction table has a type column indicating cost or revenue. Each widget can have multiple cost rows and multiple revenue rows. There is a transaction date column as well as a column for buyer id on revenue rows and supplier id on cost rows.

       

      Now comes our issue. We want to be able to slice this data and show how much revenue and profit came from a certain supplier, or buyer, etc.

       

      When we filter to look at only a certain buyer, we lose the cost rows and ability to calculate profit. Vice versa when looking at transactions from a certain supplier purchased by a certain buyer.

       

      How should I set up this extract to enable reporting like this?

       

      Thanks in advance.

        • 1. Re: Extract design to enable filtering correctly
          Jim Dehner

          Good morning Chris

          It sounds like you are trying to "dollarize" your COGS at the supplier level -

          If that is the case it becomes a pretty complex (and interesting) problem -  I don't have a data set to use that would illustrate the process -

          Would you please attach a twbx workbook with at least some dummy data that would illustrate the problem - be sure to include your to data file sheets separately - could be 2 sheets in an excel file

          Thanks

          Jim

          • 2. Re: Extract design to enable filtering correctly
            Chris Harper

            Thanks Jim, please see the attached rudimentary example. It seems you understand the issue at hand.

            • 3. Re: Extract design to enable filtering correctly
              Jim Dehner

              Good morning

               

              see the attached

              I worked with the data as you presented it - but I am not convinced that this is the analysis you really want to do - I have done inventory analysis and profitability with a number of my clients - and the typical scenario is that they are purchasing product (or components) repeatedly over time and the products are sold to a number of customers over time - but there is not a match on purchase date and sales date - further the purchase cost varies by purchase date and item ans well as the sales price - the typical question is to determine the COGS on a sale based on FIFO ( or LIFO) inventory rules and use that to determine the profit on the individual sales -

               

              Inputs usually include dates, $ and quantity as well as customer supplier and other dimensions to group / classify the profit- the analysis is pretty complex and beyond what can be present here

               

              all that said - the analysis attached just looks at what was purchased/sold and relates that to the buyer or supplier ID based on the data presented

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.