2 Replies Latest reply on Sep 11, 2018 4:14 AM by Steve Martin

    Use Calculated Field (ie Dynamic Parameter) in Custom SQL

    Steffi B

      Greetings All,

      The metric I need to calculate is revenue acquired from new subscriptions VS revenue acquired from renewed subscriptions.

       

      There is a universal relative date filter on the dashboard that I want to use on the calculation.

      This would be quite simple if only my dataset wasn't HUGE and the disk became full every time I tried to perform the above calculation with the regular data source joins, and then manipulating the result.

       

      Therefore I had to sort the revenue into "new" and "renewed" upfront, in the custom SQL data source (using 2 date parameters), here's the PostgreSQL snippet:

       

             ...

      sum((CASE when us.subscription_start  between <Parameters.Start Date> and <Parameters.End Date>

           then revenue else 0 end

        )) as "New Subs Revenue",

      sum((CASE when us.subscription_start < <Parameters.Start Date>

           then revenue else 0 end

        )) as "Renewed Subs Revenue"

      ...

       

      This works as desired - however my boss thinks the aesthetic of having a date filter (for all other graphs) but two additional parameter boxes for just this graph is unattractive and thus wants me to use just the relative date filter.

       

      I know that I can retrieve the start and end date of the relative date filter using 2 calculated fields: {MIN( [Date] )} and  {MAX( [Date} )} but you can't use calculated fields in custom SQL + parameters are static so I can't update the existing ones with these 2 calculated fields. <RANT> WHY AREN'T PARAMETERS DYNAMIC? </RANT> (here's a link to up-vote that idea by the way: https://community.tableau.com/ideas/1178)

       

      I'm using version 10.5 on a Windows machine. Thanks in advance.

        • 1. Re: Use Calculated Field (ie Dynamic Parameter) in Custom SQL
          patrick.byrne.0

          Hello Steffi,

           

          Are you still unable to achieve the desired result? I have not had much experience with working with Custom SQL and parameters in Tableau. I believe you found the Community Idea for the feature. Please continue to share and vote up the idea as it helps our product development team plan for upcoming releases.

           

          Hope this helps!

           

          Cheers,

          Byrne, Patrick

          • 2. Re: Use Calculated Field (ie Dynamic Parameter) in Custom SQL
            Steve Martin

            Well, I do have huge amounts of experince of using CustomSQL although, my data is quite small - the largest I use CustomSQL with is (currently growing daily by around 10 million rows) 7 billion rows and 120 colums wide; the smallest is 250 million rows so apologies if these tables aren't as big as yours but still, with the correctly defined and tuned query, Tableau could still render quite fast.

             

            First-off Steffi, can I ask why you haven't placed these two fields in the source sql of yhe CustomSQL statement instead of using in Tableau?

            Tableau is really the place for semi and non-additive calculations, and logic, whereas, unless the max and min are calculated exclusively from the result-set of a Tableau filter, then max and min are therfore additive and thus best placed in the source.

             

            That said, you could always use window calcs in order to get this number too eg Window_Max(Date) and Window_Min(Date) and then just scope correctly; although, I am a strong-advocate for keeping these calcs in the sql to reduce unnecessary stress on the underlying server

             

            Steve