Thanks for sharing these thoughts on the Tableau table calculations. I am assuming that you have seen the product help explaining the table calcualtions that is found in the product help correct Transform Values with Table Calculations - Tableau
Just so I am clear, is the ask here to have some sort of explanation of table calculations and the way they operate in the Tableau Desktop view in terms of nested SQL and windows functions? I just want to make I fully understand the ask so I can focus my search or provide this as feedback for the team as possibly new conent in the future.
Look forward to hearing from you!
My ask is this: Assume any table with season, grandparent_key, parent_key, child_key, time_key, value.
Take all the different sql window functions and show how they would be implemented in tableau across the different levels of the table.
Here is an example.
parent as a percent of grandparent with ranking:
select parent_key, grandparent_key, rank() over (partition by a.grandparent_key, order by a.value), a.value/b.value percent from (
select sum(value) value, parent_key, grandparent_key, from table group by 2,3) a
(select sum(value) value, grandparent_key from table group by 2) b using (grandparent_key)
What would the table functions for this be if season was in the row and grandparent_key was in the column or vice versa. same with parent_key, child_key etc stacked in a complex hierarchy or not.
I think a systematic breakdown of all these permutations and their corresponding table function would be more helpful than a "theory" on how the table function window works, because I've read a lot of those theories and I still end up guessing and checking a number of permutations until I hit successful combination
I figured something out today, because I finally had a little time to play with this again. It's not my main tool. Anyway, if you refer to the picture above, the specific dimensions that you select are the exact opposite of what you'd select if you were to write a window function in sql. so for what I wrote above, ti would be sum(value)over(partition by Acct Group 1, Acct Group 2, acct_group_p, program code)/sum(value) I don't know if this is true for all table functions.
1 of 1 people found this helpful
The best table calc explanation post I've seen is from Andy Kriebel:
It doesn't translate to SQL in the post but you should be able to translate from the sentence structure that Andy uses to the sentence structure in SQL that you mention in your post.
I tried to press examples to see if its true in action, but it failed. I can understand for each and by for % total
If I had a table with parent, child and value, then for each parent by child in a select statement, it's
select child, parent sum(value)/sum(value) over (partition by parent) from table group by child, parent
But if we do a sum, for each parent by child, what does that mean?