Sorting

Sarah Thompson

Hi all-

My question is about sorting. I use reports all the time. All.The.Time. And I'm encountering a particular issue that I don't understand that I never really noticed before.

If you sort a report, then change the report to sort a different way, I thought it would sort again with the way it was sorted before as the basis for how it's sorting now. I have no idea how to word this in a way that makes sense, so here's an example. If I sort something numerically, then do a whole new sort and sort by date instead, I would think that it would resort based on the results of the numeric order.

So, you have 5 items, and this is sorted numerically:

# column Date column

1 2/1/20

2 2/2/20

3 2/1/20

4 2/5/20

5 2/1/20

Sorted by Number it would look like above. Then if you change the sort to sort by date instead of by number, (not doing anything with the second, "then" tier of sorting, I would THINK it would do this:

1 2/1/20

3 2/1/20

5 2/1/20

2 2/2/20

4 2/5/20

But in my experience, this isn't happening. I thought it would do the new sort based on the order left behind by the old sort. Which begs the question, how does it choose the new sort when you have 3 of the same date?

Anybody notice anything like this or understand how it sorts?

Paul Newcome

You would have to use the tiered sorting. If you do not, then the data is sorted based on the pull from the source sheet(s). Not from previous sorts.

Isaac Jose

Hello everyone,

Thanks for your post and discussion! I am currently working on Sarah's support case, and I'll do my best to let you all know the resolution. In the meantime, my best guess is that there may be a hidden time value associated with the date field that is throwing off the sort.

To visualize these hidden time values, enable dependencies on a project sheet then enter =Start@row+"" into a Text/Number column to see the Date+Time value. This also works with the Finish column.

By default, each 8hr work day starts at 8am and ends at 4:59pm with a 1hr break/lunch between 12:00 and 12:59. You can use elapsed time (e.g. Duration = e8hr above) to ignore the 8am-5pm restriction.

For Sarah's example, I suspect that the hidden time on Row 66 occurs after Row 65, causing 25108 to appear after 25111. The resolution would be to ensure that the formula in "Overall Trip Departure" uses the DATEONLY Function to convert the Date/Time value into a flat Date value.

Kind regards,

Isaac J

Smartsheet Support

Paul Newcome

You would have to use the tiered sorting. If you do not, then the data is sorted based on the pull from the source sheet(s). Not from previous sorts.

Sarah Thompson

Interesting. Never realized that.

When I did the tiered sorting I still ran into issues with it not sorting numerically after sorting by date. Doesn't help that the date in question happens to be very busy for us.

Paul Newcome

The data in the # Column... How is it populated?

Sarah Thompson

It's a concatenate that combines info from a # column and a letter column. I suspected that might be the issue, so I tried sorting by the # column that the concatenate pulls from, but same results.

The sheet that it pulls from is sorted by the concatenate column all the time.

Paul Newcome

That is the issue. When you combine numbers and text, it converts everything to text.

The # column that you pull from (the second one you mentioned)... How is THAT data populated?

Sarah Thompson

It's manual.

Paul Newcome

So if you had used the tiered sorting first by date then by the second mentioned number column that is manual entry, you should have seen

1 2/1/20

3 2/1/20

5 2/1/20

2 2/2/20

4 2/5/20

which is the second result in your original post. Is that not what happened?

Sarah Thompson

Correct, it is not what happened. I had tried tiered sorting as well, by date, then by the second number column which is input manually.

Just for clarification,

-if I had had a filter on the source sheet, would that have affected the outcome on the report? I would think not for a filter but I had been sporadically using one so that could have been the cause.

- If I had changed the sort on the source sheet, that would definitely have affected the outcome of the report, correct? I don't believe I did so but I am in and out of it so much that I might have forgotten to sort it back the way I like it. Doubtful because I'm the only one consistently using it and I'm very particular about it, but it's possible.

If a filter on a source sheet impacts the sorting of a report then that was almost definitely the cause. If not I will play with it again on Monday and see what I find out.

Thanks for your help. Have a good weekend.

Paul Newcome

I revisited your original post...

In my last question I asked if you sorted by date THEN by number. I see in your original post you are sorting by number THEN by date.

If you sort by number first then by date, the date sort would not change anything unless you had duplicate numbers. So if you had two 5's, then it would have sorted those two 5's based on the date, but if none of your numbers are duplicated, then there wouldn't be a need for the second tiered date sort.

Filters and sorting would not have an affect on the report sorting unless you had NO sorting on the report. Then the report would pull in order of the source as previously discussed.

Sarah Thompson

I didn't specify what we do. We actually sort by date, then by number on the reports. I sort by number only on the source sheet.

And I said number to simplify things, but in reality we have 5 digit numbers. Each line has a different 5 digit number.

So the reality is that in my efforts to not be confusing, I probably didn't give you enough information. Because I also just realized I failed to tell you that the date column (called Overall trip departure) is formulated to pull the info off a parent line if there are children. Here's the formula: =IF(COUNT(ANCESTORS()) > 0, PARENT(), [Departure Date]@row).

We are a travel agency. We sort reports by Overall trip departure, then by Main Number, which is one of the sources in the concatenate formula. I only created the Overall trip departure formula to be able to sort trips together that actually go together but have random flights in the middle. So if for example a group is traveling to Australia for 3 months, then has a segment in the middle where they spend a week in New Zealand at the end of month 1, the associated child for for NZ will appear under the parent Australia row when sorted by date. As opposed to another group which is leaving a week before the NZ segment appearing beforehand.

So I feel like based on what you said before that I just answered my own question. Is the report having a hard time sorting by a date column with a formula in it? I've included a picture below of the primary column so you can see what i mean by the sorting issue.

We've been doing this for about a year with no issue. I only created the Overall trip departure column to be able to sort by date in a way that makes sense for us.

Paul Newcome

So long as the formula is generating an actual date (which it looks like it should be assuming the source data is an actual date), then the sort should have no problem with it.

I am assuming your Overall Departure is the first date found in each of the cells pictured above? If that is the case, then it appears as if the sort is accurate.

Each row is first sorted by date in descending order. Then each duplicate date is sorted by number in descending order.

Sarah Thompson

But that's my question. The overall departure date on all the rows above is 6/28/20. So why is it splitting up 25108A from its parent if the second sort item is by main number, which is the same for both. How is it choosing to sort the rows when the overall date is the same for all of them.

Paul Newcome

25108 A Shows "06/29/20" and not "06/28/20".

Sarah Thompson

It's sorting by the column on the left (Overall Trip Departure), which is not the same date column that pulls into the primary column. The primary column shows only the date that that particular flight leaves.

Paul Newcome

Are you able to provide a screenshot of both columns you are sorting by as well as a screenshot of how you are sorting?