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

# Calc that uses Table row in calculation (Retention Calc)

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)

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.

Mahfooj

• ###### 2. Re: Calc that uses Table row in calculation (Retention Calc)

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)

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)

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

 OrderNumber Members with Potential Orders >= OrderNumber of the Row 1 3 2 2 3 2
• ###### 5. Re: Calc that uses Table row in calculation (Retention Calc)

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

 Member Order Number Potential Orders Member A 1 1 Member B 1 3 Member C 1 3

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

Mahfooj

• ###### 6. Re: Calc that uses Table row in calculation (Retention Calc)

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.

 OrderNumber Members with Potential Orders >= OrderNumber of the Row 1 3 2 2 3 2

Does that make sense?

Sho

• ###### 7. Re: Calc that uses Table row in calculation (Retention Calc)

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)

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)

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)

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)

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