4 Replies Latest reply on Dec 1, 2016 6:14 AM by Anika Brust

    Fixing a dimension by Min([Date])

    Anika Brust

      Hello world,

       

      usually I can find an answer for my questions among all of your discussions but after using Tableau for half a year now it's the first time I cannot find someone having a similar problem although I think I'm not the first to ask.

       

      Nevertheless, this is my problem:

       

      Tracking our e-commerce business by Google Analytics I can assign most of our orders to the channel the buyer came to our site by. Most users use a lot of channels for their orders.

       

      Here you can find an example:

       

      Screenshot.png

       

      I now want to assign each user to the channel he came to our site for the first purchase. So in this example I want to have a dimension assigning all of the orders above to channel "Direct".

       

      I tried this fixed lod:

       

      { FIXED [Buyer Id], [FO Buyer]: MIN([Channel Order])}

       

      where [FO Buyer] is: { FIXED [Buyer Id]: MIN([Nth Order])}

       

      Because I use MIN and the Dimension [Channel Order] is a string, this calculation always gives back the "smallest" channel by first letter - in this case "CRM".

       

      Is there someone out there who can help?

       

      Thanks in advance and have a nice day.

        • 1. Re: Fixing a dimension by Min([Date])
          Simon Runc

          hi Anika,

           

          So would this work?...

           

          {FIXED [Buyer Id]: MIN(IF [Date Payment Complete] = { FIXED [Buyer Id]: MIN([Date Payment Complete])} THEN [Channel Order] END)}

           

          Not used the calc editor do make no guarantees I have the right number of brackets!!

           

          As it only returns a value for the [first payment date] else NULL, we are then running the LoD over this, and the MIN (or MAX for that matter) of something and NULL, is always the something.

           

          Hope that does the job, but let me know if not.

          1 of 1 people found this helpful
          • 2. Re: Fixing a dimension by Min([Date])
            Anika Brust

            It works! Thank you very much for your quick response

            • 3. Re: Fixing a dimension by Min([Date])
              Michel Caissie

              Anika,

               

              Your calculation still returns you the MIN of all the Buyer's orders channels.

               

              without testing, you could try something like

              {FIXED [BuyerId]:MIN(if [Nth Order] = [FO Buyer]  then [Channel Order] end)}

              This way it will return a single Channel Order, the MIN being itself. (you could put MAX it would give the same result).

               

              If for the first order  Nth Order is always equal to one, you can simplify with

              {FIXED [BuyerId]:MIN(if [Nth Order] = 1 then [Channel Order] end)}

               

              Michel

              • 4. Re: Fixing a dimension by Min([Date])
                Anika Brust

                Hi Michel,

                 

                sorry, I just saw your answer right now.

                 

                You are right, your formula works as well. I tried the simplified version as first order is always nth order = 1. Also, Min and Max return the same result.

                 

                But tested together both your suggestions give the same - correct! - result as you can see here:

                 

                Screenshot II.png

                 

                Anika