1 Reply Latest reply on Jun 7, 2018 9:27 AM by Michel Caissie

    Order Sequence

    Jaime Lego

      I am attempting to make a Sankey diagram that tracks the progression of products. Instead of using date or invoice number, I want to create the order sequence based on the next product purchased.

      If the customer orders the same product multiple times in a row, then all of those orders would have the same number (see order 3).

      If the customer orders more than one product in a single day, then all of the products would have the same order number (see order 4).

       

      Any assistance in creating the formula for the order sequence would be greatly appreciated. I have attached a stripped down version of my data in 2018.1.

       

      For customer number 41471, the order sequence would look like this:

      OrderSequenceExample.JPG

       

      Below is a visual depiction the order sequence of 3 customers. The table from above is customer # 41471, which is the center customer in the image below.

      Sankey.JPG

       

      Once I get the order sequence situated, I would create a Sankey similar to below. All customers would be in the viz at the same time, represented by the lines.

      The height of the product would be quantity, and the thickness of the lines connecting them would be the # of customers who purchased products in that order sequence.

      Sankey2.JPG

        • 1. Re: Order Sequence
          Michel Caissie

          Jaime,

           

          Here is the solution to your first 2 screenshot.

           

          I get the Order number with the following formula

           

          if first() = 0 then 1

          elseif ATTR([Date]) = LOOKUP( MIN( [Date] ),-1 ) then PREVIOUS_VALUE(0)

          elseif ATTR( [Product] ) = LOOKUP( MIN( [Product] ),-1 ) and MIN( [Product count per invoice] ) = 1 then PREVIOUS_VALUE(0)

          else  PREVIOUS_VALUE(0) + 1

          end

           

          where  [Product count per invoice] is

          {FIXED [Date], [Invoice Number]: COUNT([Product])}

           

          Michel