Solved: Taking lastest elements of a list

Danilo_Montuan · 07-28-2021 11:20 AM

Hi!

A simple question. How do i do to take lastest elements of a list? For example:
Supose i have this: {a , b , c , d , e , f , g , h , i , j}. So I need to generate a new list taking the lastest four elements from this list, that are {g , h , i , j}. It would be {j , i , h , g} anyway, because the order that these elements come up isn’t important to me.

Whats the most simple way to do it?

Steve

The approach I provided above can be applied generally.

Identify the criteria to choose the rows that will provide the wanted values.
Use FILTER() to find the rows that match the criteria from (1). Use ORDERBY() to order the rows as desired. Use TOP() to limit the number of rows. Store the resulting ordered list of row references in a virtual column.
Using a list dereference, derference the list from (2) to get the values from the needed column of the rows.

View solution in original post

WillowMobileSys

To clarify, you want the “highest” 4 elements from a list based on SORTED order not on date/time of entry.

To achieve this you would sort in DESCENDING order and then use the TOP() function get the top N elements. An example is:

TOP(SORT(<<list of elements>>, TRUE), 4)

TRUE - the flag that indicates to SORT the list in descending order.
4 - The top number of N elements to return

Danilo_Montuan

Well explained, but my case is different.

The list i want to take is a serie of historical data which is needed to take a average value. For example.
Number of sales on january to december months = (12, 15, 17, 16, 10, 11 ,12 , 21, 14, 12, 17, 20).
I want to give the user capacity to decide how many retroactive months will be considered on average calc. If user chooses 4 months for example, the formula would be: AVERAGE(20,17,12,14)
So I can’t use SORT() on elements, because their positions on list matters. In this case for example, i need to take the values : {20, 17, 12, 14} and not higesth values {16, 17, 20, 21}.

I keep trying here using ORDERBY(), but i am having problems with this formula and not findind documents and usage examples. See below.

I emphasize that the expression has inputed within a column in other different table of ConMensal.

Thanks a lot.

WillowMobileSys

Understood. That is a little more difficult but I believe is still doable. The ORDERBY() function orders rows based on a specified column. It returns the list of row keys from the resulting order.

You can accomplish your desired result by using a Slice that retrieves the top 4 rows from the ORDERBY() function. The you can perform a simple SELECT from this Slice.

The Slice Row Filter would be:

IN([Row Key Column], TOP(ORDERBY(History Data[Row Key Column], [Number of Sales], TRUE), 4))

Note that “History Data”, “[Row Key Column]” and “[Number of Sales]” will need to be replaced with your specific Table and Column Names.

With the Slice created and confirmed it is returning the correct Top 4 rows, you can then use a simple SELECT() to retrieve the Sales number value list.

This is the method that came to mind. Realize its not tested but I believe it will work. Maybe others have a simpler way I am not thinking of?

Marc_Dillon

@WillowMobileSystems, I think that’s the only way. You can alternatively build the List in an additional column instead of a Slice. I’d use [_RowNumber] in the ORDERBY(), in place of where you have [Number of Sales].

I was going to suggest this:

[List] - TOP( [List] , COUNT( [List] ) -4 )

However that only works if all values in [List] are unique. Given the example above, “12” exists in the older records, and so the list subtraction would remove it from the last 4 as well.

WillowMobileSys

Ah yes, thank you. I actually meant to type [Sales Month] just in case rows are not in the expected Month order. Personally I never rely on [_RowNumber]. I always assume the rows are not being retrieved the same way and therefor not always in the same order. Kind of a future-proofing concept.

Steve

In the table that will receive the computed average, create a regular (not virtual) column named Months Wanted of type Number, that is required, has a minimum value of 0, an Initial value expression of 12, and is editable by the app user. With this column, the app user may choose the number of months they want included in the computed average.
Also in that same table, add a virtual column named Months to Average with the following App formula:
```
TOP(
  ORDERBY(
    FILTER("ConMensal", TRUE),
    [_ROWNUMBER],
      TRUE
  ),
  (0 + [Months Wanted])
)
```
This column will contain a list of references to the latest months’ rows in newest-to-oldest order. The list will only contain the number of rows equal to the number of months indicated by the Months Wanted column of the same row.
Finally, again in that same table, add another virtual column named Average Over Months with this App formula:
```
AVERAGE([Months to Average][SumQnt])
```
This column will contain the computed average of the SumQnt column values of the rows referenced in the Months to Average column of the same row.

Danilo_Montuan

Hi Steve, thanks for explanation…

In an attempt to explain my problem in a simpler way to understand, I had used as example “sales by month”, but in fact i am designing a Inventory Manager able to calculate the
average consumption of stock items. So, just consider “Monthly Consumption of this item” in place of
“Sales by month”.

“Conmensal” that means “Monthly supply” is a “view only” table where i calculate the monthly supply of each item through of a query function on A1 cell.

The point is: How do I can extract a list from this Conmensal table ordened from buttom to top, and insert this list in a column inside Iten table on respective Item_ID, since ORDERBY() function takes the keys, and not the values, from Conmensal.

Sorry if it wasn’t clear but i believe you can help me TKS

Steve

The approach I provided above can be applied generally.

Identify the criteria to choose the rows that will provide the wanted values.
Use FILTER() to find the rows that match the criteria from (1). Use ORDERBY() to order the rows as desired. Use TOP() to limit the number of rows. Store the resulting ordered list of row references in a virtual column.
Using a list dereference, derference the list from (2) to get the values from the needed column of the rows.

Danilo_Montuan

guys, wait me read this document before ask anything.Thanks for while…

Danilo_Montuan

I achieved.

The solution has been create three columns in Itens Table which are: [MonthsWanted], [HistoricalSupplyKeys] and [HistoricalSupplyValues] being:

[MonthsWanted] = Non-Virtual Column where user input a value to determine average from values;

[HistoricalSupplyKeys] = Virtual Column Determined by function:
=TOP(ORDERBY(FILTER(“Conmensal”,([_THISROW]=[Item_ID])),[_RowNumber],TRUE),
[MonthsWanted])

And [HistoricalSupplyValues]= Virtual Column Determined by a deference list function:
=[HistoricalSupplyKeys][SumQnt]

thanks a lot for all posts!!!