-
1. Re: Calc that uses Table row in calculation (Retention Calc)
Mahfooj KhanMar 22, 2016 12:15 PM (in response to Sho Fujiwara)
-
2. Re: Calc that uses Table row in calculation (Retention Calc)
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 KhanMar 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 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
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)
Mahfooj KhanMar 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
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)
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.
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)
pooja.gandhi Mar 23, 2016 7:55 AM (in response to Sho Fujiwara)1 of 1 people found this helpfulSho,
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.
-
8. Re: Calc that uses Table row in calculation (Retention Calc)
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 Mar 23, 2016 10:55 AM (in response to Sho Fujiwara)1 of 1 people found this helpfulYes, 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.
-
10. Re: Calc that uses Table row in calculation (Retention Calc)
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 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!