1 2 Previous Next 15 Replies Latest reply on Jan 8, 2013 10:00 AM by Robin Kennedy

How to sort by a calculated field that is a table calculation?

Hi,

I have a table calculation that gives me the % difference between the first and last date of a range of dates. The % difference is calculated on different spreads (1year, 2 Years... ) which is controlled by a parameter. I need to be able to sort the table by the % difference as I am interested in getting only the top 20 values. Any idea whether this is possible? I don't seem to be able to get it working.

Thanks a lot!!

• 1. Re: How to sort by a calculated field that is a table calculation?

Leticia, this does sound possible. Sort gets I little tricky and is dependent on the arrangement of pills and aggregations. So it hard to help you without seeing a sample workbook. If you can't share the data, either dummy it up, or use the Super Store to mock-up something similar, and then post a package workbook.

A lot of time the solution is to create a set of two fields for sorting purposes. You can search the KB for these techniques.

--Shawn

• 2. Re: How to sort by a calculated field that is a table calculation?

Hi Shawn,

Thank you very much for replying.

Please find attached a mock-up workbook. I have sorted the table by the % Difference by clicking on "% Difference - Sort" however as I cannot set this up at issuer level as soon as I change the parameter it stops working.

Thanks,

Leticia

• 3. Re: How to sort by a calculated field that is a table calculation?

Pinging Alex. I worked on this one for awhile, but I just can't see it. Close but can seem to get there.

--Shawn

• 4. Re: How to sort by a calculated field that is a table calculation?

Do you care about showing the prior date? Here I just pulled a discrete copy of % measure over before the issuername, and then hid the null values (from the prior date) and chose not to show the column (hide headers)

• 5. Re: How to sort by a calculated field that is a table calculation?

Yep I did that too.

--Shawn

• 6. Re: How to sort by a calculated field that is a table calculation?

Can kind of get it by fiddling with the % diff calc, but the nulls are always at the top which is annoying and the Rank field breaks

• 7. Re: How to sort by a calculated field that is a table calculation?

Hi guys,

Alex\Shawn - I need to show both dates as both values are relevant.

Robin - This kind of works but is there a way to eliminate the null values? somehow adding a filter?

Thanks a lot again!

1 of 1 people found this helpful
• 8. Re: How to sort by a calculated field that is a table calculation?

Then how about another calc to pull in the prior date with a lookup (same as the % diff uses)?

• 9. Re: How to sort by a calculated field that is a table calculation?

I think my second version fixes it

• 10. Re: How to sort by a calculated field that is a table calculation?

Hi Alex,

The order in the second version does not seem correct when using column2.

• 11. Re: How to sort by a calculated field that is a table calculation?

Hmm, mine does:

You may want to show the header, sort it again, remove the sort on any of the others, and unshow the header.

• 12. Re: How to sort by a calculated field that is a table calculation?

Click show header on the Sorting pill, then click the null on the Sorting column that comes up and click exclude. The rehide the headers again

• 13. Re: How to sort by a calculated field that is a table calculation?

I like your way of doing it Robin - neater.

• 14. Re: How to sort by a calculated field that is a table calculation?

Yeah it does!! that is excellent!! thank you very much for your help guys!

1 2 Previous Next