2 Replies Latest reply on Jun 10, 2015 1:02 PM by ann.stolzman

    Using SQL to calculate difference in prior quarter (or prior year)?

    ann.stolzman

      Hi all,

       

      It's been awhile, and my SQL is a little rusty, but I'm looking to optimize a live data source when it comes into Tableau (no, I can't extract the data - we're looking at thousands and thousands of rows).  What I have currently takes about 4 minutes to load the data because of the calculations for year over year, quarter over quarter by percentage and dollar amount.  

       

      My current query is similar to this:

      SELECT

      Category, Sub-Category, Country, Market, Sub-Market, Geo, Region, Quarter, Technology, Year, SUM(Sales) as Sales

      from "(live data source name)"

      where 1=1

      and Year >= '2012'

      and Category = case when <Parameters.Category> = 'All' then Category else <Parameters.Category> end

      and Sub-Category = case when <Parameters.SubCategory> = 'All' then Sub-Category else <Parameters.SubCategory> end

      group by

      Year, Quarter, Category, Sub-Category, Country_Name, Market, Sub-Market, Geo, Region, Technology

       

      My current calculation within Tableau is as follows:

      (ZN(SUM([ACTUAL_BOOKINGS])) - LOOKUP(ZN(SUM([ACTUAL_BOOKINGS])), -1))/ABS(ZN(SUM([ACTUAL_BOOKINGS])))

      I've got duplicates of these for dollar amounts and percentages for quarter over quarter, year over year, and quarter from prior year.  So I'd be comparing 2013 to 2012 (YoY), or Q4 2013 to Q3 2013 (QoQ), or Q1 2014 to Q1 2013 (Q Prior Year)

       

      Does anyone know how to do that accurately in a custom SQL statement in Tableau? 

        • 1. Re: Using SQL to calculate difference in prior quarter (or prior year)?
          Dan Huff

          I can't help out with the SQL bit here Ann but I can suggest something that may speed things up. Have you tried just live connecting to your data source without the Custom SQL and then building out quick filters that filter the data in the same way? I ask because Custom SQL statements 1) have to run as a subquery every time you change your view such that a query executes and 2) we miss out on a lot of query optimizations that can be done by our engine or the DB.

           

          It is possible that switching to a plain live connection with filters may speed things up to where the performance is no longer an issue.

           

          Dan Huff

          • 2. Re: Using SQL to calculate difference in prior quarter (or prior year)?
            ann.stolzman

            Hi Dan,

            Actually, the case statements helped speed things up by a minute - it's a SAP HANA connection in Tableau that's notoriously slow.  I guess I should say I'm still running in Tableau 8.2 (our server isn't upgraded to 9 yet due to an issue with adding users)