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?
Solved! Go to Solution.
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.
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
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.
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?
@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.
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.
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.
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
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.
guys, wait me read this document before ask anything.Thanks for whileโฆ
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!!!
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |