
1. Re: Weighted average of percent change
Don Wise Jul 5, 2018 4:29 PM (in response to Chris Chutter)Hi Chris,
Not sure if the final values (Next Step Copy) are what you're looking for? If you can provide what they should be, this might then need to be modified to a table calc. But without the expected results, I'm just guessing at this point. This is what I came up with based on the description of the problem:

2. Re: Weighted average of percent change
Chris Chutter Jul 5, 2018 5:05 PM (in response to Don Wise)At first first glace I am not sure why your calc is returning something different than my Next Step calc. Really what I am looking for now is a way to total my final column. It should give a result of 7615.5%. Your calc to me appears that it should do exactly what mine did, yet it clearly doesn't as the values are different. I've never used total() before though.
...
Very strange. After digging into it it seems your calc really should give the same result as mine, but in one step instead of two. But for some reason the Total() is giving a different value as part of the equation than it does when all by itself. All by itself it returns 1169 as it should. But the calc does not return what it would be expected to if that were equating to 1169.
Either way, you have gotten it as far as I did. I am looking to total those values.

3. Re: Weighted average of percent change
Peter Fakan Jul 5, 2018 5:15 PM (in response to Chris Chutter)Hi Chris, like this ?
I embedded your %change calc into your next step calc and changed the table calculation to specific dimensions and added year.
HTH 18.1 attached
Peter

Problem.twbx 25.5 KB


4. Re: Weighted average of percent change
Don Wise Jul 5, 2018 5:41 PM (in response to Peter Fakan)Hi Peter,
What he's looking to do is grand total (sum of all the values in the columns) so "Next Step" for him should total to: "It should give a result of 7615.5%".
Ideas? Thx, Don

5. Re: Weighted average of percent change
Peter Fakan Jul 5, 2018 6:30 PM (in response to Don Wise)1 of 1 people found this helpfulHi Don,
I've started unpacking it but have to go play our company soccer match. Might get back to this later in the afternoon.
Not quite working yet, but I got to the calc where you can't use the % change inside the table calc (since it doesn't exist for 2017), so have converted it to a standard weighted average.
Happy if you take it from there.

6. Re: Weighted average of percent change
Chris Chutter Jul 5, 2018 11:19 PM (in response to Peter Fakan)Hi Peter, I am at home now and so do not have access to Tableau presently and can't view your file until Tuesday. I'm not sure if this is where you are going with it, but I think I see where you might be going here and I think that might work. If the Table Calc is screwing it up, remove the table calc and separate each year into its own calculated field? Never thought of that. Unfortunately complicated and will require new calcs each year, but far better than exporting to Excel and then bringing it back in. My real data spans almost two decades so this would require a lot of calculated fields. I am interested to try running with this next week. Thanks Peter. (if this is not where you were heading, please correct me)

7. Re: Weighted average of percent change
Chris Chutter Jul 10, 2018 11:47 AM (in response to Peter Fakan)Okay, so turning each year into its own calculated field and each year's percent change calc into its own calculated field and completely bypassing any kind of table calc is tedious, but it works without exporting to Excel and then bringing it back into Tableau. So the solution is far from ideal, but at least it works. Thanks Peter!

8. Re: Weighted average of percent change
Gerardo Varela Jul 10, 2018 12:07 PM (in response to Chris Chutter)Hi All,
I know this question is marked answered but you can wrap "Next Step" in a WINDOW_SUM() and get what you are after.
Regards,
Gerardo

Problem274963gv.twbx 21.1 KB


9. Re: Weighted average of percent change
Chris Chutter Jul 10, 2018 12:22 PM (in response to Gerardo Varela)Thanks Gerardo,
That is unquestionably a lot simpler. Just need to test if that works for an aspect I didn't mention which is that there are three levels of hierarchy to what I am calling 'Company' in my example. I think I can make this work though!

10. Re: Weighted average of percent change
Gerardo Varela Jul 10, 2018 1:02 PM (in response to Chris Chutter)Let us know how it turns out.
Regards,
Gerardo

11. Re: Weighted average of percent change
Chris Chutter Jul 10, 2018 3:47 PM (in response to Gerardo Varela)Yes this works and saves me creating like 30 Calculated Fields in order to avoid Table Calcs. Thanks Gerardo.

12. Re: Weighted average of percent change
Gerardo Varela Jul 11, 2018 6:34 AM (in response to Chris Chutter)You're welcome!
Regards,
Gerardo