8 Replies Latest reply on Jan 5, 2015 6:28 AM by Harley Ellenberger

# sort date ascending/descending using parameter

In the attached, I'd like to have my Date Sort parameter sort my date field on the column shelf by either ascending or descending order.

I thought I was on the right track by using a discrete INDEX() field which I would then hook up to the Date Sort parameter, but when I add the index pill to the view it removes my column grand totals.

I need to preserve both the row and column grand totals.

Any thoughts?

• ###### 1. Re: sort date ascending/descending using parameter

Hello Harley,

I tried a work around for the situation.

Hope this helps.

• ###### 2. Re: sort date ascending/descending using parameter

Rohan,

Could you post that workbook again.  I'm not able to open the one that you attached.

Thanks

• ###### 3. Re: sort date ascending/descending using parameter

Hi,

Here is the attached workbook.

1 of 1 people found this helpful
• ###### 4. Re: sort date ascending/descending using parameter

Hi Harley,

if you create a calculated field that converts the date into a number you can then multiply that number by 1/-1 depending on the sort order parameter and then put that field onto the columns shelf.

e.g. create a calculated field called "Sort Index" with the formula:

DATEDIFF('day',TODAY(),[Date (Week or Month)])*IIF([Date Sort]='Ascending',1,-1)

this would calculate the distance in days between today and the truncated data field and then flip the sort order if the date sort parameter is changed. Put this field as the first field on the columns shelf and then hide it by unchecking "show header".

Hope that helps.

• ###### 5. Re: sort date ascending/descending using parameter

Dave,

I had tried using the INDEX solution at the start.  It gets me close to what I want, but as soon as you put the index field on the columns shelf it takes away the column grand totals.  I need to keep the column grand totals in the view.

See attached with your proposed solution.

• ###### 6. Re: sort date ascending/descending using parameter

Rohan,

This could be a potential solution, but I'd like to be able to do the sort without using the parameter to show/hide the different workbooks on the dashboard.  I'll keep this in mind as a backup.

Thanks!

• ###### 7. Re: sort date ascending/descending using parameter

you have your field down as SUM(sort index) so it thinks it's a measure (albeit a distinct one).

change it to a dimension and that should solve your problem. (right click, select dimension)

you'll also need to turn the column grand totals back on once you've done this.

edit: have attached the changed workbook

• ###### 8. Re: sort date ascending/descending using parameter

That's it.  Thanks!