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.




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



          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:




          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!


          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



              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.