1 2 Previous Next 18 Replies Latest reply on Apr 19, 2018 10:14 AM by Eric Hammond

    Writing SQL in Tableau

    TaRan Wilson

      Is it possible to write SQL in Tableau, like you would for a calculated field? I know I can create a custom SQL query when connecting to data sources, but that's not exactly what I'm looking for.

       

      There is a query that is ran in SQL that gives me a percentage that my company uses in it's report. When I try to recreate that in Tableau, my calculation is always .03% off. While that doesn't seem like a lot, for what it's used for, it's a significant difference. I was thinking that if I could use my SQL query in Tableau, somehow, I could get the correct percentage. Below is the code, in case it helps someone help me figure this out.

       

      select cast (NUM.location as real) / cast (DENOM.location as real) * 100

      from (select count (*)location from locations

      where locations.status in ('OPERATING', 'Waiting - Equipment', 'Waiting - Fiber Cut', 'Waiting - GDOT', 'Waiting - Power', 'Waiting - Other', 'Waiting - Loops') and locations.type = 'OPERATING'

      and locations.billingschema = 'Standard' and (locations.subcontractor = 'BBH-M' or locations.subcontractor = 'BBH-R')) NUM, (select count (*)location from locations

      where locations.status in ('OPERATING', 'DOWN', 'Waiting - Equipment', 'Waiting - Fiber Cut', 'Waiting - GDOT', 'Waiting - Power', 'Waiting - Other', 'Waiting - Loops') and locations.type = 'OPERATING'

      and locations.billingschema = 'Standard' and (locations.subcontractor = 'BBH-M' or locations.subcontractor = 'BBH-R')) DENOM;

        • 1. Re: Writing SQL in Tableau
          Eric Hammond

          Hi TaRan,

           

          Will you please share your Tableau calculated field that is intended to have the same result as the SQL?

          • 2. Re: Writing SQL in Tableau
            TaRan Wilson

            Will a screenshot suffice?

             

            sql tableau.png

            • 3. Re: Writing SQL in Tableau
              Eric Hammond

              I see a table calculation based on a distinct count of locations - but since the name is cut off I don't know whether it considers more than that; the table calculation could be a number of different things.  Let's back up a level.

              Duplicate the sheet so that you can work on a copy without worrying about the original

              remove the CNTD(Location... table calc from the marks shelf.

              Create a calculated field for DEN: IF Status = 'operating' or Status = 'down' or  ...etc. ...THEN 1 ELSE 0 END; add it to the text tile on the marks shelf

              Create a calculated field for NUM: IF Status = 'operating' or Status = 'waiting equipment' or .... etc. ... THEN 1 ELSE 0 END; add to the text tile on the marks shelf.

               

              Are the numerator and denominator totals as expected?

              • 4. Re: Writing SQL in Tableau
                TaRan Wilson

                Thanks for the help this far Eric.

                 

                When creating the calculated fields you suggested, I do not get the expected results. What I get is the same result as before, with using CNTD(Location Count) pill. Below is the CF that I created from your suggestion.

                 

                IF [Status (Locations)] = 'OPERATING' or [Status (Locations)] = 'down' or [Status (Locations)] = 'Waiting - Equipment'

                or [Status (Locations)] = 'Waiting - Fiber cut' or [Status (Locations)] = 'Waiting - GDOT' or [Status (Locations)] = 'Waiting - Power'

                or [Status (Locations)] = 'Waiting - Other' or [Status (Locations)] = 'Waiting - Loops'

                THEN 1

                ELSE 0

                END

                 

                Then that's duplicated (minus the "down" status) for the second field. I've attached a packaged workbook. Maybe it'll help. Refer to the "testing" tab for what we've worked on here.

                • 5. Re: Writing SQL in Tableau
                  Eric Hammond

                  Hi TaRan,

                   

                  We are in luck.  Normally I can't view a workbook with my outdated version of Tableau - but today after the usual error message the workbook opened.  You workbook so that any measure that is added defaults to a percent of table calculation.  Consider changing that by returning "Percentage Of" back to None:

                  Next, I count 12 down facilities, so 1790 operational out of 1802 is 99.33%  Is this the number you need?

                   

                  [NUM]: COUNTD(IF [Status (Locations)] = 'Down' THEN NULL ELSE [Location (Locations)] END)

                  [DEN]: COUNTD([Location (Locations)])

                  [Operational]: 100*[NUM]/[DEN]

                  • 6. Re: Writing SQL in Tableau
                    Eric Hammond

                    Attached is a variation that I saved using 10.0.

                    • 7. Re: Writing SQL in Tableau
                      TaRan Wilson

                      Eric, I wasn't able to get your code to work. It gave me a syntax error, that I was missing an operator (where the colon is). 99.33% may or may not be the number I'm looking for. It's constantly changing, so I have to compare it to official percentage located in our proprietary software here at work. My boss is wanting that number to be matched in Tableau for some dashboards that we produce for our customer. The code in the OP is the query ran in SQL for our proprietary software. That's why I was wondering if I could just run a query straight from SQL in Tableau.

                       

                      And if I change "Percentage of" back to none, how do I get the percentage in my calculation? I'm still a little new to Tableau; that's how I learned to do it.

                       

                      Also, I wasn't able to open the .twbx you attached. I'm running 9.3. I tried using an online converter I found, but it didn't work (I think it is too many versions different).

                      • 8. Re: Writing SQL in Tableau
                        Eric Hammond

                        Hi TaRan,

                         

                        I am not aware of a way to use SQL statements in calculated fields.  I do think that calculated fields result in an accurate calculation that will agree with the SQL that you posted.  First, to address the reported error, "missing an operator (where the colon is)."  I could have communicated better when I gave the three calculations in my earlier post. Please try this for the numerator ([NUM]) field:

                        • From the menu at the top click Analysis, Create Calculated field...
                        • The name defaults to something like "Calculation1"; change this to "NUM"
                        • For the calculation, type "COUNTD(IF [Status (Locations)] = 'Down' THEN NULL ELSE [Location (Locations)] END)"
                        • Click OK.
                        • Use the calculated field [NUM] to show the numerator on a test worksheet.

                         

                        Let me know whether this field is valid (error free) when you enter it.  It should give you a calculated field that lets you display the numerator.  Let me know if you want to continue with a solution that uses standard calculated fields (similar to my earlier screen shot) and I am happy to help.  If instead you first need to continue the search for using SQL in calculated fields then we can wait to see whether more experienced community helpers post any ideas.

                        • 9. Re: Writing SQL in Tableau
                          TaRan Wilson

                          Eric, thanks for clarifying that calculation for me. I should have picked up on how it was written. This problem has been frustrating me for a few days now (I did a lot of trial and error and research before finally posting here). I've only been using Tableau for about nine months, so I'm still learning my way around. I do not have to use SQL in the calculated field. I was thinking that if it was possible, it would give me the result I'm looking for. I'm for whatever solution that works. Thank you for your help and patience.

                           

                          After updating the calculation to your new suggestion, it's still giving me the same numbers as the previous one (which are still off). If I run the original SQL query in DBV, I'm getting a result of 99.01%. In Tableau I'm getting 99.61%. I'm not understanding why there is a discrepancy between the two calculations. Does Tableau calculate queries differently than SQL?

                          • 10. Re: Writing SQL in Tableau
                            Eric Hammond

                            Tableau calculates queries the same as SQL.  Table calculations are a bit different and I prefer to leave those aside at least until we tie out the results to those from SQL.  Fortunately the SQL is straight-forward, so we can verify each step.  There a numerator (operational facilities) divided by a denominator (all facilities).  I understand that you are getting 99.61%, but that is a table calculation.  Will you please show just the numerator and let me know that number?

                            1 of 1 people found this helpful
                            • 11. Re: Writing SQL in Tableau
                              Eric Hammond

                              I can now explain the 99.61%.

                               

                              There are (at the time workbook was uploaded) 43,855 rows of data.  These are for 1,802 locations, but for each location there are many rows to indicate status on different dates.  Your testing tab doesn't filter on date.  It does, however, filter out the 293 records that are "DOWN" status.  The remaining status are on the sheet (as Status(locations) is on the rows shelf), but the status of "operating" is displayed while the status of "Waiting - GDOT" and "Waiting - Power" have been hidden (right-click, hide).  There is an important difference - "Down" is not part of the table as it has been filtered out, but the other entries are still part of the table as they are only hidden.  Now when you do a table calculation, it is using the numerator of 43,393 operational and dividing by 43,562 (everything but "down") which is 99.61%.  Hopefully that removes a bit of the mystery around that number.

                               

                              I expect that you need the percent of locations that are currently operational (defined as any status but "down"), which means that you only want to count each facility once, and you need the most recent (current) status for each facility. Before we tackle getting only the most recent status for each facility, I'll pause and let you confirm that we are on the right path (or correct me if I am going the wrong direction).

                              1 of 1 people found this helpful
                              • 12. Re: Writing SQL in Tableau
                                TaRan Wilson

                                Eric Hammond wrote:

                                 

                                I expect that you need the percent of locations that are currently operational (defined as any status but "down"), which means that you only want to count each facility once, and you need the most recent (current) status for each facility. Before we tackle getting only the most recent status for each facility, I'll pause and let you confirm that we are on the right path (or correct me if I am going the wrong direction).

                                That is mostly correct. There are a few statuses that I am looking to filter out (Decommissioned, Not Ready and Transition). Other than those, I'm only interested in what is not Down (which is Operating and Waiting).

                                 

                                You asked in your previous post for just the numerator. The numerator and denominator are currently reporting the same number, 99.61%.

                                • 13. Re: Writing SQL in Tableau
                                  Eric Hammond

                                  Hi TaRan,

                                   

                                  Try this:

                                  • Open a new worksheet; don't apply any filters yet.
                                  • Drag [Status (Locations)] to the Rows Shelf. 
                                  • Drag [Number of Records] to the Text tile on the Marks shelf.  As you have your default set to use table calculations (from the menu, Analysis, Percentage of, Table), the number of records will have a table calc applied and show a percentage by each status.  If for any reason you are seeing a number of records instead of a percentage, then right-click on the green SUM(Number of records) pill on the marks shelf and choose quick table calculation, percent of total.
                                  • Click on the status of "Operating", then hold the control button and click on all "Waiting" status entries.  Right-click on any of the selected status and click "Group".  This combines the desired status types into a single row/number for operational.
                                  • The workbook that you uploaded earlier didn't include decommissioned, not ready, or transition status - but if any of these exist then drag [Status (Locations)] to the Filters shelf and filter them out; then they won't be considered in the result.
                                  • That leaves the status of "Down", which you don't want to see but you want for it to be included in the table calculation.  Right-click on "Down" and choose 'Hide'.

                                   

                                  The steps above result in a percent of total for all status records, including past status of locations, and this aligns with the SQL that was posted earlier.  To get the number of locations that are currently operational (counting each station only once), use a level-of-detail calculation as a filter:

                                  • Create a calculated field, [Current Status], and Type "{FIXED [Location (Locations)] : MAX([Changedate])} = [Changedate]"
                                  • Drag [Current Status] to the Filters shelf and select "True".
                                  • Now the percent of total reflects 1790 of 1802 locations as operational (99.33%); vs without the [current status] filter the percent of total reflects 43,562 status records of 43,855 (also 99.33%, which is unfortunate as it obscures the need to filter for the current status of each facility).

                                   

                                  Please mark posts as helpful or correct as appropriate if you find them beneficial. 

                                  Thank you,

                                  Eric

                                  1 of 1 people found this helpful
                                  • 14. Re: Writing SQL in Tableau
                                    TaRan Wilson

                                    Eric, thank you for the very detailed answer. This got my figure closer to what the SQL DB is spitting out. I'm now getting a value of 99.17% from Tableau and DBV is giving me 99.43%. A difference of 0.26%, which is better than what I was getting on my own.

                                     

                                    Do you think there's anything that would help close the gap more? I've looked over what's filtered and what's available in the DB - comparing the SQL query and with what you've helped me with in Tableau - and I can't seem to find anything we're not including/excluding.

                                    1 2 Previous Next