1 Reply Latest reply on May 1, 2018 3:14 PM by Patrick A Van Der Hyde

    Finding a ratio of matches

    Neil Nielsen

      Hello everyone

      I am looking to import some data from our KPI's to a chart in Tableau and have run into some problems. Here is the SQL query:

       

      select

        count(voucher_number)/any_value(leads.count)*100

      from c_matches

       

      cross join (

      select

        count(id) count

      from c_leads

       

      where

        date(completed_at) between '2018-04-01' and '2018-04-30'

       

      ) leads

      where

        not is_credit_note and

        date(invoiced_at) between '2018-04-01' and '2018-04-30'

       

      This query returns 40.7895 (%) as a ratio.

       

      I thought I could do this easily in Tableau by using a calculated field and doing a FIXED calculation. However, I receive totally different numbers. Any help would be appreciated as this is the usual approach I take to finding ratios.

      Thanks!

        • 1. Re: Finding a ratio of matches
          Patrick A Van Der Hyde

          Hello Neil,

           

          Are you having Tableau call the SQL as a defined view or you could pass through a custom SQL query if that query can be wrapped in a Select * call.  Tableau will wrap all custom sql calls with Select * statements to prevent damaging calls to the data that include insert, drop, etc..  calls. 

           

          Another option would be to share more details here about the setup in Tableau with that field because the cross joins will need to be setup on the data panel. 

           

          Patrick