2 Replies Latest reply on Aug 25, 2013 9:34 AM by Shawn Wallwork

    Calculate Monthly occurrence only? (Part 2 of First Occurrence)

    Mark ODonnell

      Part 1:  http://community.tableau.com/thread/129906

       

      So after having some time to play with solutions provided by Jonathan Drummey & Joe Mako I am finally understanding how Tableau really works and correct me if I am wrong.   EVERY view created using normal methods with calculated fields and parameters is solely based on the current view of your data and not all of the data.  So what I am trying to confirm;  applying a filter to the data only affects the current view and if I want to use a global filter (one that will take into account all the data not shown in the view) I will need to use some type of RAWSQL?

       

      Example:  (same data as before)

      The workbook contains Job ID and Cause Value.  I added a lookup that compares the month to the prior month and if the value is >0 then the value is null.   Basically all it does is show instances of Job ID and Cause Value that did not occur the previous month.  This is slightly different than my previous post.

      sample4.jpg

      When I added a date selection for the user and a custom date range is selected my lookup operates using only the filtered data view.

      sample5.jpg

       

      I need to know what works for this scenario?  Is it RAWSQL, combined views, different method of adding the data source?  Are there any in depth blogs or tutorials available for these types of queries?

      Thanks.

        • 1. Re: Calculate Monthly occurrence only? (Part 2 of First Occurrence)
          Mark ODonnell

          I just wanted to see if any gurus have read and understand my questions, thought process?

          This week I found some links to clever tricks to "hide" columns vs. filter them but nothing that hints to my original question(s).

          • 2. Re: Calculate Monthly occurrence only? (Part 2 of First Occurrence)
            Shawn Wallwork

            Morning Mark,

             

            I'll let others answer your "am I understanding this correctly" set of questions. I only wanted to mention that "calculated fields" and "table calculations" are different animals. I think this is where some of your confusion is coming from. Here's the order of execution that Joe and Jonathan came up with awhile back (it may have been refined since then):

             

            Order of exe.png

            Here's the link.

             

            You can see that table calcs are applied late in the process after the query has been returned to Tableau. You are only applying your LOOKUP() table calc to the date-filtered data, so T is behaving as expected.

             

            The solution is to change when your Date Range Filter is applied. By creating this table calculation:

             

            LOOKUP(ATTR([DATE RANGE]),0)

             

            You move the filter from pre-query to post-query. This particular formula is a Wilson Po creation (I think) that allows you to filter out things (months) from the view without filtering them from the calculations. So previous month is being calculated correctly, without having to show all the months. (See attached.)

             

            --Shawn