8 Replies Latest reply on Jul 25, 2018 7:45 AM by Robert Bozso

    Compare custom periods

    Robert Bozso

      Hey all, I've been lurking on the forums and already got a lot of helpful insight from all the answers you provided to other's questions. However my current issue I could not find a working solution to.

       

      I want to compare the Earnings (E) of customers who have adapted our new solution. In my data set I have rows for each of the transactions and an indicator if it was generated by the New Product (NP) or the Original Product (OP).

       

      I have all sorts of calculations and tables in place that compare the (E) generated by the (NP) and the (OP), but what I want to do is compare the overall (E) since implementing the (NP). So what I want to do is somehow come up with a calculated field for Earnings since implementing the new product and Earnings before implementing the new product. The date of implementation will be different for each of the customers.

       

      I have a calculated field called NP transaction date:

      IF [New Product]=1 then

      [Transaction Date]

      END

       

      What I was trying to do is define the Earningsperiod with a calculation like this:

      IF MIN([NP transaction date]) < [Transaction Date] then

      SUM([Earnings])

      END

       

      This of course results in the aggregate vs non-aggregate battle but I can't figure out a way to fix that.

       

      Adding ATTR to the transaction date doesn't work:

       

      IF MIN([NP transaction date]) < ATTR([Transaction Date]) then

      SUM([Earnings])

      END

       

      if I try to then show the number in a table (so that it would list the earnings from each of the customers in that period) the table just stays empty.

       

      Can any of you help me with how you would go about this or just point me in the right direction so I can further research a solution?

       

      I really appreciate any input!

       

      Example of what the data looks like (lot more rows and columns but I think this is all that is relevant to the question).

       

      Customer
      Transaction DateEarningsNew Product? (1= NP, 0=OP)
      Customer105.02.201851
      Customer111.04.201860
      Customer201.01.201841
      Customer105.03.2018110
      Customer322.02.201881
      Customer315.04.201891
      Customer227.06.201840
      Customer328.06.201810

       

      P.S.: I do not have access to the desktop version of tableau, only the online one, so not sure if I'm able to attach an example worksheet.

        • 1. Re: Compare custom periods
          swaroop.gantela

          Robert,

           

          Please see if the below could give some ideas.

           

          If one put it on every row of a table with [Customer] and [TransactionDate] on Rows:

          MIN([NP transaction date]) < ATTR([Transaction Date])

           

          both the MIN and ATTR would be done only for each row at a time, not for all of the rows of the same customers.

          And so it would return the same value for both which would fail the comparison yielding the nulls.

           

          Instead, you are needing to find the MIN over a window and so you could try

          a WINDOW_MIN(MIN([NP Transaction Date])) and set it to restart every Customer.

           

          Alternatively, you can use a Level of Detail calculation to get the minimum date:

          [NewProductStart]:

          { FIXED [Customer] : MIN ( [TransactionDate] ) }

           

          Then you could include the earnings in the manner you described:

          [IncludeEarnings]

          IF [New Product? (1= NP, 0=OP)]=1 AND [TransactionDate]>=[NewProductStart]

          THEN [Earnings]

          END

          1 of 1 people found this helpful
          • 2. Re: Compare custom periods
            Okechukwu Ossai

            Hi Robert,

             

            I believe this could be done using LODs if I can understand your question. The data looks a little confusing since MIN(NP transaction date) for each customer predates the OP dates. What exactly do you want to include or exclude in the sum of earnings? Is it possible to say what the expected numbers are for the example shown?

             

            Ossai

            1 of 1 people found this helpful
            • 3. Re: Compare custom periods
              Zhouyi Zhang

              Hi, Robert

               

              Is it something like this?

               

               

              ZZ

              1 of 1 people found this helpful
              • 4. Re: Compare custom periods
                Robert Bozso

                Hello Okechukwu,

                 

                thank you for looking at this! Thank you for pointing this out, you are right the data I provided is indeed confusing.

                In reality I have hundreds of lines for each of the customers and the earliest transaction lines for everyone will be the old product.

                Then at differing dates they will start to have transaction lines for the new product but still have transactions for the old product as well. So in reality the OP dates will always predate the MIN(NP transaction date) for each customer.

                 

                What I want to include: Earnings for all transactions (OP and NP) since the first NP transaction.

                What I want to exclude: Earnings for all transactions before the first NP transaction.

                Why?: The NP has an impact on the earnings for the OP as well. I would like to calculate the Delta of what they were earning before they implemented and what they are earning now. Simply looking at the overall NP earnings doesn't take into account the "hidden impact" that the NP has on the OP.

                 

                p.s.: I also have customers that have no transaction lines for the NP, but I'm filtering them out for this the table view.

                • 5. Re: Compare custom periods
                  Okechukwu Ossai

                  Hi Robert,

                   

                  Okay, that clears things up a bit. Is the solution below what you are looking for?

                   

                   

                  Create calculated field [First NP Transaction]

                  {FIXED [Customer]: MIN(IF [New Product ID] = 1 THEN [Transaction Date] END)}

                   

                  Create calculated field [Earnings Since First NP Transaction]

                  IF [First NP Transaction] < [Transaction Date] THEN [Earnings] END

                  Hope this helps.

                  Ossai

                  1 of 1 people found this helpful
                  • 6. Re: Compare custom periods
                    Robert Bozso

                    This worked like a charm thank you! Exactly what I was looking for.

                     

                    The other two answers were helpful in pointing me in the right direction as well, but I ending up going with this one.

                     

                    Again thank you all.

                    • 7. Re: Compare custom periods
                      Okechukwu Ossai

                      You're welcome. Glad it helped.

                      • 8. Re: Compare custom periods
                        Robert Bozso

                        This explanation on why it didn't work with my logic really helps for the future. Thank you