12 Replies Latest reply on Sep 12, 2013 8:18 AM by Jonathan Drummey

    Need to create a variable with the number of items with the same transaction id

    Natalia Alfonso

      I have a transaction database that has all the items sold with a unique id and the transaction id for the transaction they were sold on (Some transactions have more than one item). I want to create a variable that says how many items were sold with an item. This could be achieved in excel by just counting how many items have the same transaction id (Count if). I dont know how to do it in tableau. My final goal is to use this new variable as a dimension (So that I can make a graph saying how sales are split according to items per transaction)

       

      Mensaje editado por: Natalia Alfonso

        • 1. Re: Need to create a variable with the number of items with the same transaction id
          Shawn Wallwork

          Natalia, welcome to the forums! I believe you are looking for the SIZE() function. Place this in a calculated field and then set compute using to [TransactionID]. This will tell you how many items were in each transaction. If you run into troubles post a packaged workbook and I'll show you how to make it work.

           

          --Shawn

          • 2. Re: Need to create a variable with the number of items with the same transaction id
            Natalia Alfonso

            This does tell me the number of occurrences associated to a transaction ID. Is like using countd over the item id. However this doesnt allow me to group variables (The end thing I want her is know how much I sold from transactions with one, two, three items). Thats why I thought I need to create an actual new calculated variable

            • 3. Re: Need to create a variable with the number of items with the same transaction id
              Shawn Wallwork

              Natalia, without a packaged workbook it's very difficult to answer these sorts of questions. Read this and see is you can come up with some dummy data. Thanks,

               

              --Shawn

              • 6. Re: Need to create a variable with the number of items with the same transaction id
                Natalia Alfonso

                How is this different from just using a countd on the attribute transaction Id

                I dont need to post in on a table. I need to create a new variable altogether so that I can filter by it / aggregate data.

                I want to filter all transactions with 4 items for example.

                I want to know how my sales divided between single items sales and multiple items sales.

                I need to create a new variable all together

                • 7. Re: Need to create a variable with the number of items with the same transaction id
                  Shawn Wallwork

                  Natalia,

                  I dont need to post in on a table.

                  Then please don't post an Excel file with a tab names "What I want" that contains a text table.

                   

                  I need to create a new variable altogether so that I can filter by it / aggregate data.

                  I want to filter all transactions with 4 items for example

                   

                  Create this calc: IF [SIZE]>=4 THEN [SIZE] END

                  And put it on the filter shelf. (Add more ELSEIFs if you want to add lesser numbers.)

                   

                  I want to know how my sales divided between single items sales and multiple items sales.

                   

                  IF COUNTD(SUM([Sales])) = COUNT(SUM([Sales])) THEN 'Single Item Sales' ELSE 'Multi-Item Sales' END

                   

                  I need to create a new variable all together

                   

                  Tableau doesn't use variables per se. It uses calculated fields and parameters. If you can better explain what it is you are really looking for and post better sample data, we will be able to better help you.

                   

                  --Shawn

                  • 8. Re: Need to create a variable with the number of items with the same transaction id
                    Natalia Alfonso

                    I understand. I think I do need to create a variable though, which is what I tried to show on the excel file.

                     

                    I want to create a pie or bar chart that shows for all my sales how they split between 1,2,3,4,5,6,7,8,9,X item sales. All you are giving me creates info on a  visual table, which while useful is not as flexible. I want to create this variable as a new dimension on my data. I really dont know what else I could share, its all on the excel file.. I just want to create that variable to use a dimension.

                     

                    I have a paid version of tableau, perhaps I should be calling a number or something? I am really thankful for the help!!

                    • 9. Re: Re: Need to create a variable with the number of items with the same transaction id
                      Shawn Wallwork

                      Natalia, the more you explain the more I understand. First let me reiterate: Tableau does not use user-defined variables, but that might just be a terminology distinction.

                       

                      Let's move over to using the SuperStore data set. Since there's more data. (I can't bring myself to create pie charts, so I'll do this example in a bar chart.) Here's a bar chart of profit divided into bins and then used to divide up the Sales:

                       

                      Bins-2.png

                       

                      Too me this sounds similar to what you're wanting to do. (See attached). Are we moving in the right directions?

                       

                      --Shawn

                      • 10. Re: Need to create a variable with the number of items with the same transaction id
                        Natalia Alfonso

                        Ok here I go again.

                        So, What I need is to create a table that allows me to break down sales by number of items per transaction.

                        Its like if I took the table on the excel I posted and created a table consolidating sales by the variable "Size along transaction" that you created.

                        Like this:

                        So remember my database has items in the rows, and then each row has a sale value and a transaction number. I want to aggregate sales by number of items per transaction (which is basically how many other items have the same transaction number). The solution I had was just use transaction number on the detail, and color it based on a variable that does a countD of Item ids. This is ok, but the graph does not allow me to have totals or do calculations like average transaction value for transactions with x number of items, or filter (Thats why I want this "Items per transaction" to be a dimention on the data)

                        Screen Shot 2013-09-11 at 10.37.23 PM.png

                        • 11. Re: Need to create a variable with the number of items with the same transaction id
                          Shawn Wallwork

                          Sorry, someone else will need to take this on.

                           

                          Cheers,

                           

                          --Shawn

                          • 12. Re: Re: Need to create a variable with the number of items with the same transaction id
                            Jonathan Drummey

                            [Hi Shawn, I'll take this one.]

                             

                            Hi Natalia,

                             

                            I think the difficulty here is that you did not specify your final goal, you specified only part of the goal, so until your last post I also didn't have an understanding what you were trying to do, and even then you did not provide sample data so I'm not 100% sure I have something to meet your goals.

                             

                            What I believe you're looking to do is to aggregate the sales for transactions by the # of items in each transaction, and then aggregate that again. This has been solved numerous times on the forums under various names, but it uses a particular setting that is not well-documented and a different approach than in Excel.

                             

                            In Excel, we might use a worksheet to do the initial aggregation for # of items for each transaction, then something like SUMIF() to do the next aggregation, or put the next aggregation on another worksheet.

                             

                            When we're looking to do aggregates of aggregates inside Tableau, we don't use one worksheet to create a variable or set of values to use in another worksheet, we do all of the nested aggregations in the same worksheet, via table calculations, and for this particular case we need to do one more thing to be able to partition the secondary aggregation (the sum of sales) by the first aggregation (count of the # of items). I set up several examples of different charts all using Superstore Sales in the attached, with Transaction ID = Order ID, Item ID = Item.

                             

                            Here's how I set it up in the workout worksheet:

                             

                            1. Used an extract so I have access to Count Distinct (which is not available for Excel/text/Access data sources).

                            2. Put COUNTD(Item) on the Rows Shelf and made it discrete (blue pill) so it will generate headers. There is 1 row.

                            3. On the COUNTD(Item) pill, uncheck "Ignore in Table Calculations." This is the special sauce that in step 7 will let us partition the sum of sales we'll create later on the # of Items

                            4. Put Order ID on the Rows Shelf to the right of the COUNTD(Item) pill. There are now 11270 rows.

                            5. Create a new Total Sales calculated field with the following formula: IF FIRST()==0 THEN WINDOW_SUM(SUM([Sales])) END. The IF FIRST()==0 part is used to ensure that we return only one value of the calc per # of Items.

                            6. Put the Total Sales field on the Text Shelf, in the Superstore English Extract you'll see $30M in sales.

                            7. Click on the Total Sales pill to set the Compute Using to Order ID. This causes the calc to be addressed along the Order ID, and partitioned (restarted) for each new COUNTD(Item).

                             

                            Now we have 11270 marks, but we really only want to show 6 marks. If you drag Order ID onto the Level of Detail to hide it, there are only 6 rows, but Tableau will show the ellipses (...) to the right because there are 11264 Null values that it is drawing. To get rid of this, in the workout (optimized) worksheet, I Ctrl+Dragged a copy of the Total Sales pill onto the Filters Shelf (so the table calc retains the prior Compute Using settings), and set it to filter for non-Null values, this can speed up performance. I also put a continuous (green) COUNTD(Item) onto the Filters Shelf since you'd mentioned needing to do that as well.

                             

                            The pill arrangements for the bars, treemap, and packed bubbles should be pretty self-explanatory, the only thing that does need explanation is why I used a continuous (green) COUNTD(Item) on the Columns Shelf for the bars. This is because if we use the discrete (blue) COUNTD(Item) pill and one of the middle values does not exist, Tableau will not draw headers for those values. I set up an example in the bars discrete (wrong) worksheet, where I filtered out orders with 3 items, so the headers go 1, 2, 4, 5, 6. In a larger data set with more variation a missing header might go unnoticed, so in the bars worksheet I use a continuous COUNTD(Item) on Colums to make Tableau draw an axis, so there will always be space for the values.

                             

                            Does this meet your needs?

                             

                            Jonathan