Ranking and Top 10 chart

[Yokuinin]

I would like to view the Top10 bar chart.

Here is 3 tables.

Item ---> Detail <--- Order

The total number of item might be approx. 100 items.

"Detail" table has the data of sales(item, qty, sales amount etc).

Here is rough image of the Item table I want to have.

No.	name	sales	rank
1	beer	120,035	4
2	red wine	95,749	5
3	white wine	75,924	10
4	sparkling wine	28,473	18
5	orange juice	62,745	12
6	apple juice	27,429	23
7	water	181,742	1
...	...	...	...
100	...	...	...

"Item" tables has "sales amount" calculated from the detail (using select() formula) (I already had this result).

I would like to also have the ranking based the sales amount.

In Excel, it may calculated using rank().

And also I would like to have the bar chart which contains only top 10 items from the table.

So I would like to know these 2 things.

(1) How to rank the listed items based calculated sales amount?

(2)How to can I make the chart contains only top 10?

[Joseph_Seddik]

You don’t need Rank. 

TOP(
    ORDERBY(table[key], [sales], TRUE),
    10
)

ORDERBY() | AppSheet Help Center 

TOP() | AppSheet Help Center 

[graham_howe]

UPDATE: I posted the following before I saw the answer from @Joseph_Seddik, he is correct that you do not need rank to simply sort and select the top 10. However, if you actually needed to show a rank then the following would work.

I've been playing around with this and have the following suggestion, note that this only works if each row has a unique identifier with a consistent length. It is possible to create a rank without this constraint, but it makes the expression more complicated. In my case I am using a key called [UUID] which has an initial value of uniqueid() and therefore will always be 8 characters in length. [rank] would be a virtual column with the following expression:

ceiling(
  find(
    [_THISROW].[UUID],
    top(
      orderby(
        detail[UUID],[sales],true
      ), 10
    )
  )/
  (len([_THISROW].[UUID]) + 3)
)

To explain what this is doing, working from the inside out:

1. order the [UUID] values of the records in the detail table by descending [sales] value

2. Create a list of the top 10 items from the order by function

3. Find the position of the current row [UUID] in the top 10 list, this will be the character position of the code

4. Divide the found position by the lenght of the [uuid] column pls three because there is always " , " between entries in a default list

5. Round up the division to give an integer which is the rank

[Yokuinin]

Thank you both of @graham_howe  and  @Joseph_Seddik  !!

Looks nice!

According your advice, I can get the "list" of top 10 items.

But still I don't know how to make bar chart including only this top 10.

It will be good for me that the bar chart will be ordered by the sales ranking.

@graham_howe  Your advice incredibly nice for me.

In my table, the ID length is not same, some is 8, some is 13.

I need to modify the formula.

[Joseph_Seddik]

You can link your chart view to a slice containing just the top 10 items. Its row filter condition would be:

IN([_This], TOP(ORDERBY(table[key], [sales], TRUE), 10)

The Rank virtual column formula can be:

IFS( 
  NOT( IN([key], sliceName[key]) ), 0,
  [key] = INDEX(sliceName[key], 1), 1,
  [key] = INDEX(sliceName[key], 10), 10,
  TRUE,
    FIND(CONCATENATE(" ,, ", [key], " ,, "), SUBSTITUTE(sliceName[key], " , ", " ,, "))
  - FIND(CONCATENATE(" , ", [key], " , "), sliceName[key]) 
  + 2
)

[Yokuinin]

Thank you for detailed explanation.

The chart contains only top 10 was be able to generate with slice!

(The order of the item is not descending)

Unfortunately, the rank virtual column does not calculate correctly,

I can't find what is wrong.

It may good for me to forget this...

[graham_howe · Accepted solution]

As @Joseph_Seddik mentioned, the use of a slice with a row filter is the best way to get the list of top 10. However the slice itself is not ordered, therefore if you calculate a ranking based upon position in that slice, you have to repeat the order.

If you then need to have a ranking formula for a virtual column, then you could go with the following:

count(
  split(
    left(
      top(
        orderby(
          table[key],[sales],true
        ), 10
      ),
      find(
        [_THISROW].[key],
        top(
          orderby(
            table[key],[sales],true
          ), 10
        )
      )
    ),
    " , "
  )
) - 1

This will actually work without the slice, but if you have the slice in place then the following would work, but note I am repeating the sorting:

count(
  split(
    left(
      orderby(
        sliceName[key],[sales],true
      ),
      find(
        [_THISROW].[key],
        orderby(
          sliceName[key],[sales],true
        )
      )
    ),
    " , "
  )
) - 1

[Yokuinin]

Thank you for your update!

It seems good.

Can I have some confirmation?

(1) At the end of the formula (both of 2 you illustrated), you set "-1".

The result of the formula,

the 1st place item would be "0", the 2nd place item would be "1".

Is this because of "-1" at the end of the formula?

(2)Both of the formula, the rest of TOP10, which result do these item have?

In my trial,

11th --- 0

12th --- 0 

all of the rest(other than top10) is "0".

Is my trial result is right?

(3)To make the bar chart ordering by ascending,

how should I set the value ?

in current time, I get the value of the order in the table of "item" and its in the virtual column.

[graham_howe]

(1) The -1 is because of the way I am sorting and the list is getting converted to a string. Without it I believe I was seeing2 to 11, but try it out. If you are seeing 0 to 9 then yes, get rid of the -1.

(2) That is correct, the result of the count will be zero for anything outside the top 10.

(3) Changing the true to false will switch from descending to ascending, however you need to do that after you have isolated the top 10, otherwise you would end up ranking the bottom 10!. If you use the second expression in conjunction with the slice, then that would be fine, the slice would restrict the list to be just the top 10, then the subsequent expression could ranking them in the opposite order.

There is one weakness to my approach which I should let you know about. It does work with unique IDs of differing lengths, however it will fail if one ID can exist completely in another. For example if you had ID AB123456 and AB12345678 then the expression will fail to give the correct ranking.

[Yokuinin]

Thank you so detail information.

Now I am leaving my PC.

As soon as I am back, I will check.

[Yokuinin]

I had understood (1) and(2) in my question.

(I don't know why the result of yours and mine are different...)

(3), could you explain where I can set the sorting order?

It seems there are no sorting in columns, slice and UX chart, even though "true or false".

https://help.appsheet.com/en/articles/961513-controlling-row-sort-order 

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Hi-I-m-trying-to-sort-the-data-in-my-chart-i/m-... 

[Yokuinin]

In UX option, turn on the "Preview new features", 

I could be able to use "the new Chart editor".

I can control the chart order now.

Thanks and regards,

[erasmo]

Mr. Graham congratulations for your very, very nice answer. I was trying to ranking 368 cities and I could'not to build some good solucion like yours. Thanks a lot.