3 Replies Latest reply on Oct 13, 2018 8:11 PM by Ken Flerlage

    Highest rank item as attribute of dimension

    Eric Viglotti

      I am looking to take order details data and interpret the top sales product purchased and call it the "primary product" so that I can attribute that to the entire order for analysis. For example, I have isolated a few order ID's in the Sample Superstore data (attached and below):

       

      Screen Shot 2018-10-13 at 3.44.48 PM.png

       

      Using this example, I want to interpret the AT&T EL51110 DECT product as the "primary" product for Order CA-2014-100006 which is obvious since it's the only product. However, for the next order, CA-2014-100090, I want to interpret the HON 2111 Invitation Series Corner table as the primary product for that order. My end goal is to consider that an attribute of the order so that I can total all sales for the orders by primary product. Using this example, I would be able to say that for orders where the HON 2111 Invitation Series Corner table was the primary product, the total sales was $502+$197 = $699.

       

      Any ideas how to accomplish this?

       

      Thanks!

        • 1. Re: Highest rank item as attribute of dimension
          Ken Flerlage

          To do this, we'll first find the sales amount for each order/product combination using the following calculated field:

           

          Sales by Order & Product

          // Get the sales amount for each order/product

          {FIXED [Order ID], [Product Name]: SUM([Sales])}

           

          Next we'll find the max of the above for each order:

           

          Max Sales by Order

          // Get the maximum sales amount (by product) for each order.

          {FIXED [Order ID]: MAX([Sales by Order & Product])}

           

          Finally, we'll find the product whose sales matches the max sales for the order.

           

          Product with Max Sales

          // Find the product who's sales match the max sales for this order.

          // As some products could have the same sales, we'll take the overall MAX.

          // We also want to get one product for each order, thus the LOD.

          {FIXED [Order ID]: MAX(IF [Sales by Order & Product]=[Max Sales by Order] THEN [Product Name] END)}

           

          Then you could build the view you're suggesting. For example:

          See attached workbook.

          1 of 1 people found this helpful
          • 2. Re: Highest rank item as attribute of dimension
            Eric Viglotti

            This is outstanding, thank you! Using this, I was able to create a visualizationthat did the following (as noted in the caption of the attached):

             

            In Super Store data, there are 5,009 distinct orders. By splitting these orders by the "primary product" purchased, we can estimate the product that was most likely the reason for the customer's visit. Using this information, we can see what percentage of sales on these orders was consumed by this primary product as opposed to other high sales added to the order. The tooltip hover gives insight into the complete details on these orders.

             

            For example, the Hon 4070 Series Pagoda Armless Upholstered chair often had other high value items on these orders such as other chairs and, in one order, an iPhone.

             

            Below is a screenshot of one of the items. This is very useful, so thank you so much for the clear instructions and workbook!

             

            Screen Shot 2018-10-13 at 7.32.32 PM.png

            • 3. Re: Highest rank item as attribute of dimension
              Ken Flerlage

              Great. Glad it worked.