Reverse Sort

Solved6.37K viewsFormulas and Functions
0

Ok, here is my problem.

I have a long list of inflation numbers, and I need to rank them. However, using the normal rank function, it assigns the best (1) value to the highest inflation area, and visa versa.

Is there a way to reverse this? Tks.

Carl

1

The RANK function has an optional third argument… either 0 or 1.

If order is 0 or omitted, value_list is ranked in descending order (e.g. 34, 10, 6, 2, 1). Otherwise, value_list is ranked in ascending order (e.g. 1, 2, 6, 10, 34).

Hope this helps!

-Mike

0

A developer just brought another sorting technique to my attention:

– Click on the ‘% Bal per State’ column.
– Hold the SHIFT key down, then click and drag to select all of your STATE category items (UT – ID)
– Now with this constrained selection in place, do your ASC or DESC sort. It will sort based on State.

Your groups will still get broken down into individual lines, but I think this presentation is closer to what you are looking for.

-Mike

0

I think your best bet is to do the sort, then move the ‘State’ category up to the filter tray. In this mode you will be ‘slicing’ your model by state to get the state specific information you are looking for.

If anyone else has a different approach, please feel free to post.

-Mike

0

Mike,

Great to hear from you. Yeah, I am looking to sort the column ‘% Bal per State’ from highest percentage to lowest, without changing any other formating to the columns to the left.

In other words, within each state, let me sort the percentages so that only the columns Doc_Type_Desc, Current Bal, % Bal per State will be sorted, but the columns State and All Doc Types stay the same. The numbers, percentages, and descriptions would only shift within the All Doc Types box.

Sorry if I am not explaining this very well.

Carl

0

Hey Carl,

When you specify the ASC sort, you are sorting the % Bal per State for All states. Therefore it has to “break” the grouping in order to rank the %Bal per State from highest to lowest across all the states.

If you are looking for a different type of sort, please post again with your specific needs.

-Mike

0

Pete,

Thanks for your reply. I think your point about collapsed items was part of the problem. So that helped.

The other part relates to how when I first filter the column, and then do ASC sort, the grouping that I did to the left gets broken down into smaller rows. That is also causing the problem. Can someone take a look, and give some suggestions? Thanks!

Carl

0

Carl,

I believe what you are seeing is the rows collapsed from the filter you have set on that column. When sorted, those rows all come to the top and appear as a block of collapsed items. You can eliminate this by selecting View->Hide Collapsed Items from the menus.

Let me know if that does the trick!

Cheers,

pete

0

Ok, the day would not be complete without a posted question from me, so here goes.

In this model, I am trying to sort the ‘% Bal per State’ column, in the Doc Type + Desc matrix. However, when I click on the column heading, and do a sort, I get an ungodly amount of rows created. Can anyone give me a tip? Thanks.

Carl

0

Carl,

Checking “blank cells under a category assume previous value” allows you, for example, to import a table such as the one in the attached CSV and assume that each blank line under the field Company belongs to the company above it (when defining Company as a category).

Sometimes data is in this format (e.g., outline format), and this options helps in those cases.

Chris

0

Chris,

Thanks for responding. I did take a look at that sort, but it offers only ASC or DSC. What would be nice would be to do some custom sorts, such as <not equal 0>, <great that X>, etc.

How does the check box “blank cells under a category assume previous value” work? If I have this checked, it reads to me like there would be duplicate values when in fact the program should assume that the value is zero. Can you explain? Tks.

Carl

0

Carl,

One other option: I notice that that you created the data matrix in your Housing Oversupply model through DataLink. When you define a category (such as Date in this case) through DataLink, you have the option to sort the items that category upon import. If you edit your datalink, you can click through the wizard to the point where you define your input columns as categories or items, and add a sort to Date.

Chris

0

Good Question. The first column you are referring to is actually the items belonging to the date category. So when you are applying a sort to the ‘value’ item, it is actually sorting on the Atlanta (the first) item in your Metros dimension.

That being said, your goal is to sort by date. You can manually set the sort by clicking on one of the dates, and using the ctrl-arrow combination on your keyboard to move the dates up and down. Make sure you clear any existing sorts on your value item first (click on the value item, Tools > Sort Data > Clear Sort).

If you want something a bit more automatic, you can create a new item in your Metros dimension and call it ‘DateSortKey’ or something like that. Then you would write the formula:

DateSortKey = value(@Date)

The @Date tells Quantrix to bring the text of each item into each corresponding cell, and the value function turns that text value into a number. Then the item is formatted (Format > Number > Date/Time) and then sorted.

This is kind of a long winded answer. I have attached a model file demonstrating this. Please post again if you need further clarification.

-Mike

0

Ok, I have another weird problem.

I have a table with the first column as dates, and when I try to sort them, they don’t fall into chronological order. It looks like it is just treating them as plain numbers, or something.

I have attached the file. Can someone take a look and see what is wrong? Tks.

Carl

0

No problem Carl. For everyone question that is asked in the forums – there are many more wondering the same thing. Questions posted on the forum helps us spot trends on where our application help can be improved upon. So post away!

-Mike

0

Mike,

You must be getting tired of my basic questions :-). Thanks for the tip. My manager has requested 5 commercial licenses, so hopefully, we will be up and running with Quantrix soon.

Carl

0

Ok, I used a work around of multiplying the inflation numbers with -1, and then sorting. Is there a more “elegant” solution to this? Tks.