5 Replies Latest reply on Nov 27, 2018 5:51 PM by morton hsiao

    Table functions once and for all for people familiar with sql

    morton hsiao

      I can't handle the across the pane, down the pane in the element etc.  It's too tricky.  Here's a table calc window:

      HHOYfga.png

      In case the image didn't load, because it's not on my screen, here's a link:

      https://pasteboard.co/HHOYfga.png

       

      Anyway.  I understand window functions.  Like a running total over a month that resets every year.  If I had a month and a year field it would be:

      sum(x) over (partition by year order by month)

      and if I had a bunch of items by transaction, it would be

       

      select year, month, value, sum(value) over (partition by year order by month)  from (select year, month, sum(value) from table group by year,month) or some such.

       

      Did someone ever describe all the different clicky boxes and drop down fields in the language of nested sql and window function because nested sql and window functions are easy.  Everyone and their mother knows that fresh out the womb.  Just like the cube function   Click boxes, drop down lists with or without the calculation assistance really requires a Pomona cs degree to figure out.

        • 1. Re: Table functions once and for all for people familiar with sql
          patrick.byrne.0

          Hello Morton,

           

          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!

           

          Cheers.

          Byrne, Patrick

          • 2. Re: Table functions once and for all for people familiar with sql
            morton hsiao

            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

            join

            (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

            • 3. Re: Table functions once and for all for people familiar with sql
              morton hsiao

              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.

              • 4. Re: Table functions once and for all for people familiar with sql
                Paul Wachtler

                Hi Morton,

                 

                The best table calc explanation post I've seen is from Andy Kriebel:

                Tableau Tip Tuesday: Table Calculations Overview

                 

                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.

                 

                Best,

                Paul

                1 of 1 people found this helpful
                • 5. Re: Table functions once and for all for people familiar with sql
                  morton hsiao

                  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?