7 Replies Latest reply on Aug 26, 2016 8:11 AM by Simon Runc

# why the sorting is not correct

Dears

can someone help check why the sort is not correct? it is not sorted by descending. thanks

• ###### 1. Re: why the sorting is not correct

hi Johnson,

So the 'sort' functionality works across everything in the view...so if you removed your [Year] field, you'll see that it is sorted correctly (from a Tableau behavior point of view!).

There are a few ways round this...If you want to sort the bars by the FY 2016 measure...one way is to create a calculated field which only contains the 2016 measure, so;

[Sort Field]

IIF(YEAR([Date]) = 2016, [sales], 0)

btw this isn't very dynamic, as you'd need to change the formula to say 2017 next year, but we can get around that...I just want to show you the general principle here.

If you then set this new field to be your sort (as a SUM) that should do the trick.

Hope that makes sense, and works...let me know if not

• ###### 2. Re: why the sorting is not correct

Thanks Simon,

the sort field don't work here since my "Date" field need chagne to Fiscal year which acorss 2015 and 2016,

e.g. 12/2015 is Fiscal FY16

do we have any other dynamic way,

• ###### 3. Re: why the sorting is not correct

Hi Johnson,

In your dimensions pane right click your date field that you've already changed the fiscal year on.

Go to create -> Custom Date

Then choose Years and select DatePart and Year

Then use this field in your calc field.

IF [DateField (Year)]  = 2016 THEN Sales ELSE 0 END

And use this as your object to sort on.

Best Regards,

Carl Slifer

InterWorks

1 of 1 people found this helpful
• ###### 4. Re: why the sorting is not correct

OK....well we're going to have to get a little creative!

So I can force the YEAR (DATEPART) function to act like FY, by fooling Tableau!, using the DATEADD...

I create a field which returns the YEAR in fiscal year, with the following (you can change the -3 for your starting FY month -1);

[FY Year (starting April)]

and then I can proceed, as before with this field.

[Sort on FY 2012/13]

IIF([FY Year (starting April)] = 2012, [Sales],0)

and use this on the sort. The 'How it works' tab, shows how the [FY Year (starting April)] works.

Let me know if that does the trick!

• ###### 5. Re: why the sorting is not correct

Thanks Carl...I always forget about that option!

Yes Carl's method is much better (...if not as fun!). I've updated the attachment to show both (but I'd go with Carl's)

...Just out of curiosity I wanted to see what the custom date (generated) was doing...

It's fooling itself with the same trick!!!

• ###### 6. Re: why the sorting is not correct

thanks Carl and Simon,

it does works now, but I'm still looking forward a daynamic way instead of new a individual field to sort.

• ###### 7. Re: why the sorting is not correct

So in terms of dynamic...I assume you mean that you don't want to hard-code 2016 into the formula?

We can fix this by using a FIXED LoD

so in Carl's version, if you switch out the 2016 with the following {MAX([DateField (Year)])} this will be the last year of the data (as defined by FY...also {MAX([DateField (Year)])}-1 would be the year before and so on)

So would be

IF [DateField (Year)]  =  {MAX([DateField (Year)])} THEN Sales ELSE 0 END

and so this calc updates to sort by last year, whatever the data.