11 Replies Latest reply on Mar 17, 2017 9:21 AM by Joe Oppelt

    How to create combinations within a table

    Thijs Jahae

      Dear people,

       

      I have the following problem. I have information on individual products that are sold. A gin and tonic in a bar are separately described in my data set while when they are on the same receipt they could well be in a mixer. I want the amount of mixers that are sold during a certain period. I will sent an example file where I want to Count the amount of mixers and the total mixer revenue. In the next example I want to count 3 mixers because I assume that when these products are on the same receipt they are in a cocktail.

       

      Schermafbeelding 2017-03-16 om 15.45.21.png

       

      You could really help me out if I could automate this for multiple cocktails.

      Thanks in advance

        • 1. Re: How to create combinations within a table
          Joe Oppelt

          I have this open, but I don't understand what you are looking for.

           

          Given what you see in Sheet 1, what numbers do you want to derive?

           

          If a third mixer shows up in your data what do you want?  (Suppose there is a drink called a Southern Cooler, and it requires gin, tonic and Southern Comfort.)

          • 2. Re: How to create combinations within a table
            Thijs Jahae

            Yeah the assumption is quite rough but it is all that we can do right now.

            I just want to count in the whole database the amount of gin tonics that were served.

            So i do want to know the amount of tonics that are served, the amount of gins that are served & the combinations (when they are together in a drink). (I also want to know the total revenues)

             

            Because nobody in my city drinks just Gin I assume that all the time Gin's and tonic's are on the same receipt they are together in a cocktail.

             

            I hope this clarifies it a bit

            • 3. Re: How to create combinations within a table
              Joe Oppelt

              OK, I'll try again.

               

              Given the data in the sheet, do you want to see a total of 3 for "Gin", 5 for "Tonic", and 3 for Gin&Tonic, since there seem to be three orders where both Gin AND tonic are in the same row?

               

              (What if BON=2 showed 2 for Gin and only 1 for tonic?)

               

              I'm trying to drill down on the necessary logic it will take to achieve your goal.  You're not helping me with general statements.

               

              I doubt your actual workbook will have only two values for [Description].  What if one condition requires two specific descriptors and no others, but a row has those two PLUS another descriptor?

               

              And why am I asking this?  Because 99% of the time when someone provides an answer that satisfies a very limited example, the follow-up response says, "Well that works for the example, but my actual workbook has (these other) conditions..."

               

              I want to help you.  Help me to do that.

              • 4. Re: How to create combinations within a table
                Thijs Jahae

                True,

                 

                'Bon' Means receipt.

                On receipt one there is 1 gin and 1 tonic

                On receipt two there are 2 gins and 2 tonics.

                etc.

                 

                So in total we sold 4 gins and 5 tonics from which 3 gin/tonics were in mixers

                 

                in the case of 2 gins and 1 tonic I assume that we sold (2 gins, 1 tonic from which 1 gin/tonic mixer (secondary information)

                 

                And you are completely right the descriptions are in the real database more volatile (see if I want to count vodka/red bulls we have descriptions like: rb, red bull, Red Bull, Bull, etc.)

                 

                I want to take it step by step and thought these issues could be solved by creating sets or grouping the data after I know how to combine and count the mixers in general.

                 

                I hope again that this clarifies it a bit.

                 

                Thanks

                • 5. Re: How to create combinations within a table
                  Joe Oppelt

                  The attached workbook is a start.

                   

                  Sheet 1 uses LOD calcs to separate your various descriptors.  You need that to compare whether specific ingredients are used.

                   

                  For demonstration purposes, I made Sheet 1a to show how the price of gin or tonic is treated if descriptors are added to the sheet.  I did averages in [Prijs of Gin or Tonic per Bon] because if there are 3 Gins and only 2 Tonics in a particular Bon, we have to have some way to assign a single price to an ingredient.  If the price is consistent for all three Gins and both Tonics, then the price is easy to derive, but if two drinks were charged 500 for the Gin, and another was charged 400, and we have to exclude one of them because only 2 tonics were sold, which one should we exclude?  So just do an average.

                   

                  Sheet 3 gives you totals.

                   

                  The [Gin AND Tonic] calc doesn't factor in the possibility that a 3rd ingredient was also used in the drink (making it something other than a plain old Gin and Tonic.)  If that can happen, and that drink should not be counted, the logic in this calc will need to be modified.

                  1 of 1 people found this helpful
                  • 6. Re: How to create combinations within a table
                    Thijs Jahae

                    He Joe,

                     

                    That was great advice! It worked.

                    I applied it now to the more complex database and you were right about the new complexity.

                    I have two additional questions and it would be great if you could help me with those as well.

                    Schermafbeelding 2017-03-16 om 19.30.09.png

                    Schermafbeelding 2017-03-16 om 19.30.21.png

                     

                    I combined all the vodka and red bull descriptions in the Count measures. The data of the first picture is the underlying data of the first row in the second picture. The price of the 'red bull or vodka' column should be 780 (300 rb and 480 vodka) These are all from the same bar. The numbers seem not to add up since it also takes the spa red into account. Do you know how we can solve this?

                     

                    2. A simpler question: how do i get the grand total at the end and can I use these figures in other sheets?

                     

                    Thanks for your help again!

                     

                    Gr

                    • 7. Re: How to create combinations within a table
                      Joe Oppelt

                      Re-create the data adding in the Red Bull and Vodka and Spa Rood so that I can play with it.  Repost a workbook with that in it.

                      • 8. Re: How to create combinations within a table
                        Joe Oppelt

                        As for the grand total, you'll need to re-calc it in other sheets.  The totals shown on the sheet are created by the built-in TOTAL function, and are display only.  But you can create your own calc, either doing a WINDOW_SUM or LOD to use them in other ways.  You can't pass them from sheet to sheet, but you can re-calc them in other sheets and use the calc values any way you need.

                        • 9. Re: How to create combinations within a table
                          Thijs Jahae

                          Ok great thanks for the help. Here is the workbook with the red bull vodka data

                          • 10. Re: How to create combinations within a table
                            Thijs Jahae

                            Ok so if I for instance want to make a Pie chart of all the vodka's and the part of it that was in vodka red bulls. I will need to make another column as well to exclude from the 'prijs of vodka' column the 'prijs of vodka' that was not in the red bull mixer but was on the Bon. I think that is also where the adding goes wrong in the last columns I think?

                             

                            After this I can window_sum this column and put this together with the total sales of vodka in a pie chart? Thanks again

                            • 11. Re: How to create combinations within a table
                              Joe Oppelt

                              Let's do one step at a time.

                               

                              The calc named [Prijs of Red Bull or Vodka] should really be named [Prijs of Beschrijving per Bon].  When I first made it, I named it based on the data in the example.  This new name more accurately reflects what it does.  (For now I'm just leaving it named as it currently is.)

                               

                              If the price of an ingredient is the same across all Bons in the data, we could instead do:

                               

                              { fixed [Beschrijving] : avg([Prijs]) }

                               

                              But I suspect prices change over time, so Bons will have different prices for a given component over time.

                               

                              So the calc as it currently stands is probably the smarter way to leave it.  And for any given Bon, you will have the price that was charged for all the Vodkas that day.  This is the current syntax:

                               

                              { fixed [Bon#], [Beschrijving] : avg([Prijs]) }

                               

                              That is saying, for each Bon, and for each component within that Bon, get the average price.

                               

                              Look at Sheet 2.  I captured just  the first three Bons from the first sheet.

                               

                              When we look at 27094, we see that the two components we're concerned about are the only two components, and they are also equal.  The simple logic from the original example works here.

                               

                              For 83354, there are 3 components, and the number of Red Bulls do not match the number of Vodkas.  My original logic does the best it can do in determining the number of RedBull-and-Vodka orders.  It assumes that nobody who ordered a red bull did not drink it straight -- they always got it with Vodka.  If you're OK with that, we can move forward with the next step.

                               

                              Look at [Prijs of Vodka & Red Bull]

                               

                              sum([Prijs of Red Bull or Vodka]*[Vodka & Red Bull])

                               

                              This is taking the sum of the price of ALL components on this Bon (computed in [Prijs of Red Bull or Vodka]) and summing them up.  that means it is including the price of Spa road too.

                               

                              What we need is a calc that captures for every Bon the price of Red Bull only.  And another one for Vodka.

                               

                              One way to do this is what I did in [Prijs of Red Bull per Bon] :

                               

                              { fixed [Bon#] :

                              avg(if [Beschrijving] = "Red bull fl." then [Prijs] end) }

                               

                              Now, for every Bon, you have the price of Red Bull in a special calc.

                               

                              (Special note here:  I notice you have multiple possible values for "Red Bull".  You really want to clean that up somewhere first.  I just used "Red bull fl." in that calc, but really you will have to do all the "OR"s everywhere, and that can get messy.  Either unify the value in your data source before shipping it to Tableau, or create a calc that unifies all the possible combinations -- for all the components -- and then use that calc everywhere instead of [Beschrijving] itself.)

                               

                              But at this point I made a different decision.  I changed [Prijs of Red Bull or Vodka] to do this:

                               

                              { fixed [Bon#], [Beschrijving] :

                              avg(if [Beschrijving] = "Red bull fl." or [Beschrijving] = "Wodka" then [Prijs] end) }

                               

                              (Again, the Special Note above applied here.)

                               

                              It's a lazy hack.  For the sake of simplicity, now I'm setting the price of only two components.  All the rest get NULL.

                               

                              Now [Prijs of Vodka & Red Bull] does a correct SUM of the two components, because those are the only two that have prices per component.

                              So in the attached, the calcs are again correct.

                               

                              If you are only looking for Red Bull and Vodka, this does it.

                               

                              If you're going to need other combinations to report on, you will have to do a component-by-component series of calcs like [Prijs of Red Bull per Bon].