11 Replies Latest reply on Mar 23, 2016 12:38 PM by pooja.gandhi

    Calc that uses Table row in calculation (Retention Calc)

    Sho Fujiwara
    Sho Fujiwara Mar 22, 2016 11:47 AM

      Hi all,

       

      I have a tricky retention report I'm trying to create to no avail.

       

      I have a data source that tracks orders by member, listing each member's nth order, along with the Value of that order. I also have an aggregate measure of Potential Orders at the member level, which shows how many orders the member could have made (without going into too much detail, this is mostly based on when they joined. So a member that joined last week has had less Potential Order opportunities than a member that joined 4 weeks ago).

       

      For each Order Number, I want to track how many members have made that many orders as a percentage of members that could have made that many orders.

       

      The first part of the requirement is easy - I can just place Order Number on the shelf and count the Number of Records.

      However, this alone misses the nuance of Potential Orders. It looks like retention from Order 1 to 2 is 66% (2 of 3 the total members have made a second order), but only 2 members (Members B and C) have had an opportunity to order twice (in fact, they've had 3 opportunities). What I need is a calculation that counts the number of members that could have made 1, 2, and 3 orders, based on their Potential Orders. I can then divide the count of records by this calculation to get the % I need.

       

      The calculation that I'm thinking would be something like:

       

      if [Order Number] < [Potential Number] Then 1 ELSE 0

       

      where [Order Number] is the value from the Table, while [Potential Number] would be the value at the member level. However, as far as I know I can't mix fields from the Table with fields from my data source.

       

      Is there any way I can calculate this?

       

      Thanks,

       

      Sho

        • 1. Re: Calc that uses Table row in calculation (Retention Calc)
          Mahfooj Khan
          Mahfooj Khan Ambassador Mar 22, 2016 12:15 PM (in response to Sho Fujiwara)

          Hi,

           

          You can use LOD expression to calculate that.

           

          create a calculated field and put this formula. You will get the potential order count at member level.

           

           

          I hope this might help you.

           

          Mahfooj

          • 2. Re: Calc that uses Table row in calculation (Retention Calc)
            Sho Fujiwara
            Sho Fujiwara Mar 22, 2016 12:24 PM (in response to Mahfooj Khan)

            Hi Mahfooj,

             

            Thanks for the quick reply. However, I don't think this solves my problem. Counting the distinct Potential Orders at the Member and Order Number level just outputs 1 for each Member/Order Number. What I need is a count of the members that have a Potential Orders <= Order Number shown in the table.

             

            Sho

            • 3. Re: Calc that uses Table row in calculation (Retention Calc)
              Mahfooj Khan
              Mahfooj Khan Ambassador Mar 22, 2016 12:42 PM (in response to Sho Fujiwara)

              Try this

               

              COUNTD(if [Calculation1]<=[Order Number] THEN [Member] END)

               

              Note: [Calculation1] is your LOD which i have shared in my earlier post.

               

               

              Mahfooj

              • 4. Re: Calc that uses Table row in calculation (Retention Calc)
                Sho Fujiwara
                Sho Fujiwara Mar 22, 2016 12:56 PM (in response to Sho Fujiwara)

                Hm, I'm still not sure what you mean. Can you send me an example of what the resulting table would look like?

                 

                I'm looking for something like this as an output

                 

                    

                OrderNumberMembers with Potential Orders >= OrderNumber of the Row
                13
                22
                32
                • 5. Re: Calc that uses Table row in calculation (Retention Calc)
                  Mahfooj Khan
                  Mahfooj Khan Ambassador Mar 22, 2016 9:14 PM (in response to Sho Fujiwara)

                  Hi Sho,

                   

                  If I'm not wrong your counting members with potential orders>=OrderNumber of the row that is fine. My question is

                  If I see the Order 1 I'm getting three members who have potential orders>=OrderNumber

                   

                  If I'm selecting Order 2 I'm getting two members

                   

                   

                  And for Order 3

                   

                  If I'm not wrong then output should be like

                  Order    potential orders>=OrderNumber   

                  1          3

                  2          2

                  3          1

                                        

                  MemberOrder NumberPotential Orders
                  Member A11
                  Member B13
                  Member C13

                   

                  Can you just explain how you're getting your output.

                   

                  Mahfooj

                  • 6. Re: Calc that uses Table row in calculation (Retention Calc)
                    Sho Fujiwara
                    Sho Fujiwara Mar 23, 2016 6:54 AM (in response to Sho Fujiwara)

                    Right, what you're doing is the first part of my intended calculation, which is calculating how many people have made their nth order.

                     

                    As you show

                    - 3 Members have ordered their 1st order (A,B, C)

                    - 2 members have ordered their 2nd order (B, C)

                    AND

                    - 1 member has ordered their 3rd order (C)

                     

                    However, I need to divide these counts by the number of members that COULD HAVE made those orders, which you can infer from the Potential Orders at the member level.

                    - Member A could only have a 1st order, because their Potential Order is 1

                    - Member B AND C could have a 1st, 2nd, or 3rd order, because their Potential Order is 3.

                    However, we see that Member B did not make a 3rd order, while Member C did.

                     

                    So, the logic of the table I want is:

                    All Members could have made a 1st Order, so the output for OrderNumber 1 is 3.

                    Members B and C could have made a 2nd Order, so the output for OrderNumber 2 is 2.

                    Members B and C could have made a 3rd Order, so the output for OrderNumber 3 is 2.

                     

                    OrderNumberMembers with Potential Orders >= OrderNumber of the Row
                    13
                    22
                    32

                     

                    Does that make sense?

                    Sho

                    • 7. Re: Calc that uses Table row in calculation (Retention Calc)
                      pooja.gandhi
                      pooja.gandhi Mar 23, 2016 7:55 AM (in response to Sho Fujiwara)

                      Sho,

                       

                      Drag potential orders to dimensions and create a calc like:

                       

                      {fixed [Potential Orders]: countd([Member]) }

                       

                      Place order number on rows and this new calc on text on marks card.

                       

                      1 of 1 people found this helpful
                      • 8. Re: Calc that uses Table row in calculation (Retention Calc)
                        Sho Fujiwara
                        Sho Fujiwara Mar 23, 2016 9:34 AM (in response to pooja.gandhi)

                        This worked! Thanks Pooja.

                         

                        I am confused by though as WHY it works.

                         

                        {Fixed [Potential Orders]: countd(Member)}

                         

                        My assumption from seeing this calculation is that it would count the number of members that have [Potential Orders] = 1, 2, and 3. So, I would expect this calculation for Orders = 2 to be 0, since no Member has a [Potential Orders] value of 2.

                        • 9. Re: Calc that uses Table row in calculation (Retention Calc)
                          pooja.gandhi
                          pooja.gandhi Mar 23, 2016 10:55 AM (in response to Sho Fujiwara)

                          Yes, you are correct, it is indeed counting number of members who have a potential orders of 1, 2 and 3 but grouping by how many orders they placed. So if you see below:

                           

                          Members A, B and C placed 1 order but 2 (B and C) of those 3 members had the potential of placing 3 orders.

                          Members B and C placed 2 orders and both of them had the potential of placing 3 orders.

                          Member C placed 3 orders and had the potential of placing 3 orders.

                           

                          BUT, because we are fixing member count at the level of potential orders, members B and C get counted towards 3 potential orders, regardless of if they placed 3 orders or not. If you place potential orders on rows and the calc I suggested on text, you indeed get 0 for 2 potential orders because no one had the potential of placing 2 orders.

                           

                          So even if member B is not present in last line, the value of 2 would be retained regardless of members and/or potential order being on the view and thats the beauty of using LODs. You can easily get rid of pills not required but still consider them in calculating the expected results.

                           

                           

                          Hope that makes sense, also if this does answer the question, please be sure to mark my answer correct and close the thread, LOD is a pretty hot topic, this would let others looking for a similar answer help land at the right place.

                           

                          Pooja.

                          1 of 1 people found this helpful
                          • 10. Re: Calc that uses Table row in calculation (Retention Calc)
                            Sho Fujiwara
                            Sho Fujiwara Mar 23, 2016 12:31 PM (in response to Sho Fujiwara)

                            Thanks Pooja. This was super clear and explained the logic perfectly. I've used LoD calculations extensively (or so I thought), but the way you used it opens up possibilities I hadn't even thought of.

                             

                            Many thanks,

                             

                            Sho

                            • 11. Re: Calc that uses Table row in calculation (Retention Calc)
                              pooja.gandhi
                              pooja.gandhi Mar 23, 2016 12:38 PM (in response to Sho Fujiwara)

                              Awesome! I am glad that helped. LODs indeed was a game changer in Tableau. Many beautiful things are possible in Tableau!