-
1. Re: Calculating a percent based off of a targeted row value
Alex Kerin May 3, 2012 3:37 PM (in response to John Liska)You may find the method here useful: http://community.tableau.com/thread/117765
Have a look at that, then we can work from there
-
2. Re: Calculating a percent based off of a targeted row value
John Liska May 4, 2012 7:52 AM (in response to Alex Kerin)Alex,
Thanks for the quick (and useful) reply. Although it doesn't exactly solve the issue I'm facing it gives me an idea for another problem I'm working through.
My original example was not completely clear so I've created a new one. Because I don't know how many products are in a set or how they will be indexed I won't be able to use the lookup() and first() logic you pointed out. I will know what my "anchor" products are because they will all be marked. (In my new example this is column C.) If I'm able to potentially propagate the anchor volume out into a calculated field (in my example: column E) then they final calc is really easy. And this allow the final % calculation to work dynamically at different levels of aggregation which is one of my goals.
Let me know what you think. Thanks.
-
example_data.xlsx 12.5 KB
-
-
3. Re: Calculating a percent based off of a targeted row value
Alex Kerin May 4, 2012 8:51 AM (in response to John Liska)1 of 1 people found this helpfulOnly one anchor SKU for each geography?
If so, you can still use lookup because we can use AnchorSKU as the ordering field.
It turns out that you can only sort a boolean field by count, but count somehow works - pushing true to last()
-
4. Re: Calculating a percent based off of a targeted row value
Alex Kerin May 4, 2012 8:57 AM (in response to Alex Kerin)Workbook....
-
anchor.twbx.zip 9.3 KB
-
-
5. Re: Calculating a percent based off of a targeted row value
John Liska May 4, 2012 9:07 AM (in response to Alex Kerin)Great stuff, thanks Alex. However, to answer your first question: no, there may be more than one anchor SKU. As such for each roll-up (in this case, geography) it would be the SUM of volume for the anchor SKUs. I created a new example Excel file which includes a Northeast geography which shows what I mean.
Sorry for the confusion. In my effort to make the example relatively easy to understand I've not included all the relevant details.
-
example_data.xlsx 15.2 KB
-
-
6. Re: Calculating a percent based off of a targeted row value
Alex Kerin May 4, 2012 10:01 AM (in response to John Liska)Okay, in this case we have a window_sum of anchor SKU values across the partition. In this case it's set to geography. You would have to change the [percent] partitioning if you had another dimension you wanted to compare across.
-
anchor 2 AK.twbx.zip 23.9 KB
-
-
7. Re: Calculating a percent based off of a targeted row value
John Liska May 4, 2012 10:13 AM (in response to Alex Kerin)That ... is ... frickin' ... awesome. Thanks Alex!! Very much appreciated.