2 Replies Latest reply on Feb 15, 2016 4:09 AM by Steve Martin

    Sql Over() in Tableau

    Stephen Gardner

      Hi,

       

      I'm new to Tableau - and looking to execute a query within Tableau similar to the two SQL statements below. Is this possible?

       

           count(1) over (partition by LN_ID) as MOD_CT

           count(1) over (partition by LN_ID order by LN_WRKOT_CLSNG_DT) as MOD_NUMBER

       

      If so, what aggregations should I use in a calculated field? Thanks in advance!

        • 1. Re: Sql Over() in Tableau
          Tableau kumar

          We use "Partition" option to divide the result into multiple parts. ( It is equivalent to ADDRESSING & PARTITIION in tableau)

          Let me take an example of creating Ranks.

          Imagine, There are three Fields like Country, State, Sales. And want to create ranks Based up on Sates Sales Volume.

          If you don't use Partition to create Ranks,  it will calculate for all states  irrespective of Country.

           

          If you implement Partition on Country, Ranks will be created separately for each country.

          http://viztableau.blogspot.in/2015/12/how-to-create-ranks-in-tableau.html

           

          Best Regards

          Kumar

          • 2. Re: Sql Over() in Tableau
            Steve Martin

            Hi Stephen,

             

            I should add to Kumar's response, these types of partitioning and addressing calcs are usually reached by creating a window calc, a straight-out example will be:

             

            1. Drag Region to Columns
            2. Product Sub-Category to Rows
            3. Sales to text
            4. Create a calc: Window_Sum(Sum([Sales)) and double-click to your table
            5. Without adjusting any calc, this should default to Table Across and provide the total sum across all-regions for each Product Sub-Category

             

            This has partitioned Over each Sub-Category, notice how your window calc has a delta symbol indicating that it is a table calc?

             

            Now, right-click the window-calc pill, hover-over Compute Using and select Product Sub-Category, notice how the value for the window sum for each of your regions is the same on each row? This is the sum of sales over Product Sub-Category and region or rather:

             

            Sum([Sales) Over (Partition By [Product Sub-Category], [Region]) It is possible to set an order by from the advanced menu if this too is required: Right-click the pill, select Edit Table Calculation From Compute Using select Advanced and then Setup the partitioning and addressing fields followed by the sort option.

             

            I hope this helps

             

            Steve