1 Reply Latest reply on Jan 21, 2015 10:23 AM by Patrick A Van Der Hyde

    rawsqlagg -> parameters that are filtered?

    Samuel Jobin

      Hi

       

      I have a workbook where the data set is pretty simple :dates and scores (ok, it's much more complex than that.  But let's pretend, it will be easier to explain).  I'm filtering on the date (so December for instance) and showing the score.  I would like to also show the score of the previous month but since the filter is on the date, and that I'm using this filter everywhere in the workbook, I,m having a hard time getting my needed value.

       

      I found this wonderful piece of information : http://www.theinformationlab.co.uk/2013/01/14/maintaining-percent-of-total-whilst-filtering/

       

      The first 2 solutions are out of questions (would be really impractical).  So I'm left with the 3rd option; RAWSQLAGG_INT function.  Here's what I'm playing with at the moment:

       

      Calculated field Test contains this : RAWSQLAGG_INT("Select score FROM my_table WHERE DATE_ID = (%1)-1", [DateID])

       

      (DateID is a INT, so Nov is 11, Dec = 12, etc).  (I know it wouldn't work on change of year, not the point here).

       

      I'm getting an error where I'm returning more than 1 row in my query.  My understanding then is that Tableau doesn't apply the filter (I'm filtering on DateID=12 in my workbook) while doing the RAWSQL function, therefore it doesn't work.  My understanding of Tableau though was that filters are applied before the creation of the calculated field.  So I must work somehow...

       

      Any thoughts?  Getting desperate here!

       

      thanks

      sam

        • 1. Re: rawsqlagg -> parameters that are filtered?
          Patrick A Van Der Hyde

          Hello Samuel Jobin,

           

          Sorry for just getting to this but I see this as a unanswered post from December and wanted to reply. 

           

          In your post, you mention that solution 1 (table calc as a filter) and 2 - using duplicate data sources, are impractical.  I'm very curious why this is.  The duplicate data source one contains another connection to the data source but does not imply duplicating the space of the data source.  Is it impractical because of the size of the data?  In addition, what about the table calculation solution does not work?  Usually, filtering  a view with a function such as Lookup() works quite well to show only a current month while maintaining the values in the view to do percent of year or percent of total calculations. 

           

          A bit more detail about the first two issues might lead to a potential solution.  As for RAWSQL functions, they are applied without filters to arrive at the data passed back to Tableau.  

           

          Patrick