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

# Filtering Based on FIXED Calculation

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

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:

 Store Date Sales A 1/1/2018 1000 A 1/2/2018 500 A 1/3/2018 900 B 1/1/2018 100 B 1/2/2018 200 B 1/3/2018 500 C 1/1/2018 10 C 1/2/2018 5000 C 1/3/2018 50

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

Ah - brilliant! Thanks so much!

• ###### 3. Re: Filtering Based on FIXED Calculation

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.