3 Replies Latest reply on May 1, 2018 1:07 PM by Joshua Milligan

    Filtering Based on FIXED Calculation

    David Schermbeck

      Hello -

       

      In Prep, is it possible to filter the data based on the results of a FIXED Calculation? Or, is there a similar functionality to get the same result?

       

      I'm finding that Prep can do everything except this. I'm having to output the Flo, and then filter based on the FIXED calculation in Desktop.

       

      Thanks!

      David.

        • 1. Re: Filtering Based on FIXED Calculation
          Joshua Milligan

          David,

           

          Yes!  You can simulate a FIXED level of detail calculation in Tableau Prep fairly easily.  (see Latest Snapshot in Tableau and Maestro | VizPainter  for an example of how to filter to the most recent date per item - which is one type of LoD)

           

          Here's another example, using this data set:

            

          StoreDateSales
          A1/1/20181000
          A1/2/2018500
          A1/3/2018900
          B1/1/2018100
          B1/2/2018200
          B1/3/2018500
          C1/1/201810
          C1/2/20185000
          C1/3/201850

           

          Let's say you want to filter each store to the record containing the highest sales (maybe to determine the date of highest sales, or some other analysis)

           

          In Tableau, you'd write an LoD calc like:

          [Sales] = {FIXED [Store] : MAX([Sales]}

           

          When you get a true result, you've got the record with the highest sales.  (It's possible to get ties)

           

           

          In Tableau Prep, you'll follow a pattern like this (which in fact is the way Tableau does FIXED LoDs behind the scenes):

           

           

          (just a warning, the explanation below seems more complicated than it is to actually do it - once you're used to it, it feels very natural)

           

          The Data step looks like the data set above.  The Max Sales per Store is an Aggregation step set to group by Store and give the Max Sales value:

           

          That will give you one record per store containing the maximum sales amount.

           

          That Aggregation step is then joined to the previous Data step in the flow on both Store and Sales.

          The result is 3 rows: one per store with the maximum sales and the other data, such as Date, that was in the original row.  And thus you've essentially filtered based on an LOD:

           

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Filtering Based on FIXED Calculation
            David Schermbeck

            Ah - brilliant! Thanks so much!

            • 3. Re: Filtering Based on FIXED Calculation
              Joshua Milligan

              David,

               

              After writing the above, it occurred to me that you can actually see the parts of the calculation in the Flow.  Don't know if it helps, but it was an "aha!" moment for me.