1 2 Previous Next 23 Replies Latest reply on Jul 15, 2016 7:30 AM by Łukasz Majewski

    Find product sold together with another product.

    Allen Gapara

      I need your help. I have tableau desktop 9.1 and have a problem with my logic/ calculations. <> or != with strings does not seem to work. I am calculating other revenue in addition to other columns. Product price minus discount given and then multiply by qty.

       

      The product is a product sold with 10000005675 OR 10000005676 OR 10000005677  But not ( 10000005706, 10000005707, 10000005708, 10000005709). The figures that I am getting are all wrong. I have broken it down as follows. First all bookingIds with must have products:

       

      Next I exclude certain products

       

      Then I combine these 2 calculations with unit Prices

       

       

       

      The Results that I get ignore <>, or !=. I have tried sets, LODs but nothing is working.

      I am comparing results with SQL and Excel, and it shows I have wrong Results. I have attached the workbook.

        • 1. Re: Find product sold together with another product.
          Michael Hesser

          Hi Allen;

           

          Is your logic correct?

           

          It looks like [Christmas BookingID] products (lets call that set {A} ) are an excluded subset of [Christmas BookingID2] (let's call that set {not A, not B}).

           

          Your first line of OtRev is essentially asking for the intersection of {A} and {not A}, which will always be null.

           

          Additionally, it looks like there is some additional counter-intuitive logic in your OtRev calculation: if [Product Code] can only be a single value, won't checking if it is NOT X OR NOT Y always return a TRUE value?

           

          Am I reading this correctly?

           

          Two other items I noticed-- it looks like you are setting values to "Null" as text. Is this your intent, or did you want null, the non-value?

           

          Also: your Christmas Filter allowed all values to pass... did you just want TRUE?

           

          Thank you!

          • 2. Re: Find product sold together with another product.
            Allen Gapara

            Thanks for looking at the problem. [Christmas BookingID] looks at BookingIDs with Christmas food. Only when christmas food is bought do we look at what else was bought but not drinks. Christmas food can be bought with drinks and other items. We are only interested in other items bought together with Christmas food.

             

            [Christmas BookingID] list all the orders with Christmas food. From this BookingID list I want a list of products without Christmas food [A] and without Drinks [B]. If I was looking at BookingIDs {not A, not B} would be a problem, but I am looking at products bought with Christmas food as well.

             

            I am not sure, whether I have explained it fully. A BookingID can have several different products. There is only one column of productID.

            • 3. Re: Find product sold together with another product.
              Michael Hesser

              Hi Allen-- I understand!

               

              You might want to check out some of the great posts on Market Basket Analysis... they'll have better details than what I can supply. Here's how I approached your problem (I'm sure many people could do it in a lot fewer steps!)

               

              I created a set of Must Have Products:

               

              I dropped this onto my filters so we see only these Product Codes.

               

              Next I created a set of products to be excluded in the calculations:

               

               

              Then I created a quick revenue calc that calculates the revenue only if a Product is not on the exclusion list:

               

              And last, I made an LOD calculation

               

              I threw these together like this:

               

              (I added some fancy symbols just so I could make sure the Must Haves were the only thing highlighted).

               

              I'm not sure if this is the format you were looking for... and frankly, I'm not sure the calcs are working the way you want, but please have a look!

               

              If you need the workbook, I can upload!

              • 4. Re: Find product sold together with another product.
                Allen Gapara

                Thanks for your suggestion. Putting the products codes on the filter creates other calculation problems. Other columns that I need depend on these different products. I can not use filters to select certain products since all the products codes are needed on this same table but different columns.

                 

                I also tried to use LOD and I ran into trouble using Fixed since it ignores the date filters on the filters. I have tried using include and exclude and then gave up. Thanks for your help.

                • 5. Re: Find product sold together with another product.
                  Michael Hesser

                  Hi Allen-- I'll be happy to keep trying to help, but I'm not sure exactly what you would like your screen to look like. Please don't give up-- if I'm not able to help you, there are many, many here who can lend a hand!

                   

                  Another suggestion: If all products need to be displayed, why not create a test to see if a Booking ID contains a Must Have?

                   

                  You can drop this in as a filter and choose only "1"

                   

                  I hope this offers food for thought!

                  • 6. Re: Find product sold together with another product.
                    Allen Gapara

                    Thanks for your help. I am expecting something like this:

                      

                    VenueOtherRevenue
                    Chelmsford B104.17
                    Stevenage B499.18
                    Yeovil16.67
                    Basildon C0
                    St Albans19.99
                    Hanley-99.96
                    Bristol0
                    Hull195.84
                    Basildon 7

                    1260.74

                    The other columns that are to be included are revenue generated by the different product codes. For example 10000005675 has one, and 5706/5707/5708/5709 will form one column.

                    I got the above figures using SQL checked them, and are correct.

                     

                    SELECT Venue, SUM(([Product Price]-[product discount given])*[Quantity]) AS OtherRevenue

                    FROM (

                    SELECT

                        [OrderId]

                          ,[BookingId]

                          ,[Occasion]

                          ,[Venue]

                          ,[Venue_no]

                          ,[Enquiry Created]

                          ,[Event Date]

                          ,[Order Status]

                          ,[Booking Status]

                          ,[order sources]

                          ,[Order Created date]

                          ,[Payment Made]

                          ,[Product]

                          ,[Product code]

                          ,[Product Price]

                          ,[Quantity]

                          ,[product discount given]

                          ,[Total Order Value]

                          ,[TimeStamp]

                          ,[Sales Person Email]

                      FROM [AuditTrail]

                      WHERE [BookingId] IN (

                      SELECT [BookingId]

                          FROM [AuditTrail]

                      WHERE [Product code] IN ('10000005675', '100000005677', '10000005676')

                     

                      )

                      AND

                      [Product code] NOT IN ('10000005675', '100000005677', '10000005676', '10000005706','10000005707', '10000005708', '10000005709' )

                      )x

                      GROUP BY Venue

                    • 7. Re: Find product sold together with another product.
                      Michael Hesser

                      ==Update==

                      I was very excited about this, but a closer inspection shows not all numbers match yours (Basildon, for example, has values that

                      exceed your number... but I believe this is because a single Booking ID includes two Must Have products.)

                      Maybe you (or someone else?) can work out the bugs? Sigh.

                      ==========

                       

                      Please try this!

                       

                      • I neglected to include one of the exclusion product in the set; I added this one
                      • I also unchecked the Exclude checkbox.
                      • Then I made a GROUP of Product code:

                      • I dropped this on the columns, [Venue] on the rows, and SUM([Revenue by Bookng ID]) into the Text.

                       

                      This produced your Other Revenue values for each of your Must Have products, as well your 4-group set.

                       

                      • 8. Re: Find product sold together with another product.
                        Allen Gapara

                        When I created a Data sample, I did not want to include the other columns. I wanted to simplify the question since I already had the other columns. There is one column [Other Revenue] which is a problem. The other Revenue column includes Revenue from product codes bought together with (10000005675  OR 10000005677 OR 10000005676 ) but not revenue from  (10000005706 OR  10000005707 OR 10000005708 OR  10000005709 OR 10000005675 OR 10000005677 OR 10000005676 )

                         

                        Products sold without (10000005675  OR 10000005677 OR 10000005676 ) are not of interest. Revenue from

                        (10000005706 OR  10000005707 OR 10000005708 OR  10000005709 OR 10000005675 OR 10000005677 OR 10000005676 )  has already been accounted for.

                         

                        What is missing is now Revenue from other Products. The whole exercise is based on only these 3 products. (10000005675  OR 10000005677 OR 10000005676 )

                         

                        Thanks for your help, what version of Tableau, do you have. If I understand what you have written, you have combined all the products except the main 3 into Others and have not filtered to what was only sold with the main 3.

                        • 9. Re: Find product sold together with another product.
                          Allen Gapara

                          I am getting some results, but they still include values of the main items (10000005675  OR 10000005677 OR 10000005676 ) , which should be excluded. I have changed the following

                           

                          and

                          Which gives me

                           

                           

                          VenueOtREV
                          Basildon C£0
                          Basildon  7£2,746
                          Birmingham£237
                          Bournemouth£150
                          Bristol£115
                          Cambridge K£105
                          Cardiff P£179
                          Chelmsford B£426
                          Colchester A£83
                          Crawley£250
                          Dundee£200
                          Edinburgh£533
                          Hanley C£0
                          Hanley F-£100
                          Hull£1,697
                          Norwich£0
                          Nottingham£246
                          Plymouth£125
                          Portsmouth£0
                          Romford F£2,683
                          St Albans£63
                          Stevenage£499
                          Stourbridge C£0
                          Swansea V£1,375
                          Tamworth K£0
                          Watford O£307
                          Yeovil£17
                          York K£213

                           

                          Still wrong, but getting closer.

                           

                           

                           

                           

                           

                           

                           

                          • 10. Re: Find product sold together with another product.
                            Łukasz Majewski

                            Hey,

                             

                            I used your SQL code for guidance and got the following results:

                             

                             

                            Cheers,

                            Łukasz

                            • 11. Re: Find product sold together with another product.
                              Michael Hesser

                              Hello Allen;

                               

                              Before we get too carried away, I'm going to ask you to re-inspect your data regarding some of the figures. In the case of Chelmsford Bar, you list 104.17 as the correct figure.

                               

                              I studied the data, limiting it first to only those Booking Id's that had one of the 3 Must Have products in them.

                              Next I calculated what the Revenue would be for that product (disregarding if it were to be excluded or not).

                              If the Product was to be excluded, I adjusted the product's revenue to 0.

                              Then I summed.

                              The value I got for Chelmsford Bar was 376.58 rather than 104.17 (though 104.17 was maximum value associated with this venue).

                              Can you review and see where the calculations are going wrong? I've attached an Excel spreadsheet showing my calculations.

                              I just want to make sure we are on the same page when it comes to calculating values: no sense chasing our tail if the initial values are off for some reason.

                               

                              I'm using Tableau 9.3.

                              Process wise-- I made two sets: one of MUST HAVE products (just the 3), and a second set of exclusions (7 products total).

                               

                              For each product, I test if it is to be excluded, and assign it the value of 0; otherwise I calculate the Revenue amount ([Product Price]-[Product Discount Given])*[Quantity]

                               

                              You can filter to only the [Booking Id] that contain Must Have products a couple of ways. I did it with a calculation that says:

                              Dropping this onto Filters and limiting it to 1 means only those Booking Ids that include a Must Have product make it through.

                               

                              The groups I created are because I thought you might want to see revenue for:

                              {each of the 3 Must Have products, the 4 other products (combined), everything else under the sun}.

                               

                              Please let me know if you have questions or if these answers help you on your quest.

                              • 12. Re: Find product sold together with another product.
                                Allen Gapara

                                Thanks Michael, for looking at my problem. If we look at Chelmsford, the difference is BookingID 1311618 which does not not fall within our Christmas dates. I am sorry that I did not mention them. These Christmas sales must have Event Dates between 21 Nov 2016 to 31 December 2016. In the Christmas filter I have these dates and that it has to be 'PartPaid' and 'Booked'. Sorry about that and thanks for for help.

                                 

                                For BookingID 1311618 the Event Date was 18 June 2016.

                                • 13. Re: Find product sold together with another product.
                                  Allen Gapara

                                  Thanks Lukasz. I am trying to figure out why we do not agree on 5 of the figures. Maybe my SQL and Tableau are both wrong.

                                  • 14. Re: Find product sold together with another product.
                                    Łukasz Majewski

                                    If the results do not match then either underlying data is different or there is a typo in the code, e.g. you might have missed '0' in on of the codes.

                                    1 2 Previous Next