4 Replies Latest reply on Nov 7, 2016 11:46 AM by Daniel McIntyre

# Sorting by Max Year

Hi Community,

I have a requirement where I need to sort my data by the most recent year selected by the user. In the attached example, I have the superstore data descending sorted by 2015 sales. If the user chose to unselect 2015, I would like the report to sort descending by 2014. If the user unselected both 2014 and 2015, I would like the report to sort descending by 2013. And so on.

Is this possible? Perhaps some sort of formula or calculated item might help?

Thank you,

Dan

• ###### 1. Re: Sorting by Max Year

hi Daniel,

So one way to do this is via an LoD. I first create the following calculation

[Sales Sort on Last Year]

IIF(YEAR([Order Date])={MAX(YEAR([Order Date]))},[Sales],0)

So this only returns a value if the Year is the same as the MAX year (for the dataset)...and so when it's SUMed it just contains the SUM of the last years sales.

Now as we've used a FIXED LoD here, it's not responsive to filtering (due to LoDs being calculated before any dimension, such as Order Year, are applied). We can get round this by making the Year filter a "context" filter (which bumps it up the calculation pipeline.

You can then use this field in your Sort for State.

Hope that makes sense and does the trick...but let me know if not.

5 of 5 people found this helpful
• ###### 2. Re: Sorting by Max Year

This is amazing! Thanks Simon!

I'm going to have to do some learning on LoDs and their uses - but your answer solves my question! THANKS!

• ###### 3. Re: Sorting by Max Year

Excellent...yes they are very useful functions.

As their name suggests...it allows us to FIX a calculation at a pre-determined level, irrelevant of the Viz LoD (and this value is then returned against every row in the data, at the level specified). So for Example the FIXED LoD

{FIXED [Customer Name]: MIN([Order Date])} returns to every row (ie. for each customer) their first purchase date.

In our case we just want the MAX of everything, and so we specify no dimension to run it over (this is also why we can drop the keyword FIXED....{FIXED : MAX(Year([Order Date]))} would have done exactly the same)

I always found the following link a handy introduction...it shows, by way of, 15 example how they work (as well as their cousins INCLUDE/EXCLUDE)

you may also find this useful, explaining (or attempting to explain!) the different calculation types in Tableau

1 of 1 people found this helpful
• ###### 4. Re: Sorting by Max Year

Wow - thank you again. I've got a little reading to do!