Get last n rows from a linked table

So I have a parent ->> child one-to-many relationship.

I want to get data from the last n rows of the child table for any row in the parent (specifically I want to get the mean of the last n rows).

Help?

Hi Craig - How do you define “last n”?

A particular sort order? The table order? By creation date and time?

P

Good question - the last n defined by a date column.

Child table has two columns - Date and Value. Both are entered manually.

Hi Craig,

It is vugly, but I have an answer for you here.
Last n numbers in a table · Rambling Pete's Quick examples.

Lots of room for kaizen, but it at least calculates the correct answer at the moment.

Regards
Piet

Thank you! I think I understand what you are doing. Slice() is pretty interesting function.

Okay - I modified this a bit bc I don’t use created date and multiple rows with different parents could have the same date. I made the Rank column a combo of the Parent Name and the Rank (thisRow.Metric.Name+‘-’+rank(thisRow.Date,thisTable.Date ).

Next question - it seems this gets the Earliest 5 rows, not the latest. What do I change for the latest?

Figured that out - added Ascending = True to the Rank column formula

1 Like

I am certain there are a few improvements that the more experienced guys could make, I particularly dislike the fact that I have hard-coded five addRows() to populate the temp storage table…

I despaired of getting this done at one stage yesterday, but got it done in the end and learned quite a bit.

I don’t see where AddRow adds the Value column, yet somehow it does?

runactions(
AddRow(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 1),/*Endf Nth */
[Temp storage].Parent ,thisRow.Parent),/*Endaddrow */
AddRow(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 2),/*Endf Nth */
[Temp storage].Parent ,thisRow.Parent),/*Endaddrow */
AddRow(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 3),/*Endf Nth */
[Temp storage].Parent ,thisRow.Parent),/*Endaddrow */
AddRow(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 4), /*Endf Nth */
[Temp storage].Parent ,thisRow.Parent), /*Endaddrow */
AddRow(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 5),/*Endf Nth */
[Temp storage].Parent ,thisRow.Parent) /*Endaddrow */
) /endrunactions/

It is one of the things that I could not figure out how to do.

So once the above is done, there is a formula in the value column that filters out the value.
image

I am absolutely certain that it is possible to skip this step, and directly populate the Temp Storage table. I just ran out of steam. :wink:

This eliminates the need for the formula on the Value column in Temp storage.

image

Next is t figure out how to use Slice and ForEach together…

Thank you. It appears that AddRow uses the display column from Last n Numbers for the value - any way to specify the column? I’ve tried using a ‘.value’ column name but it doesn’t work…

Figured that out! I had to put the .value after the filter…

Combining FormulaMap with RunActions.

This works (albeit always uses the 1st value)
FormulaMap(List(1,2,3,4,5) ,runactions(AddRow(
[Temp Storage],
[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=nth(thisRow.[Last 5 Metrics Tracking], 1 )).Value,
[Temp Storage].[Metric Name],thisRow.Name))/*Endaddrow */
)

But this doesn’t (I try to use CurrentValue of the list):
FormulaMap(List(1) ,runactions(AddRow(
[Temp Storage],
[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=nth(thisRow.[Last 5 Metrics Tracking], CurrentValue)).Value,
[Temp Storage].[Metric Name],thisRow.Name))/*Endaddrow */
)

Coda keeps saying CurrentValue can match a current value, or a row from a table.

Not sure how to reference the CurrentValue of the list…

Figured it out, kinda wonky…

I created a control and then update and reference that control:

RunActions(
SetControlValue(Seq,0),
List(1,2,3).ForEach(RunActions(
SetControlValue(Seq,Seq+1),
AddRow(
[Temp Storage],[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=Nth(thisRow.[Last 5 Metrics Tracking],Seq)).Value ,
[Temp Storage].[Metric Name],thisRow.Name) /End AddRow/
)))

1 Like

Thanks to this article by Christiaan Huizer, I was able to figure out how to do this without a temp table.

Happy to share if anyone wants.

3 Likes

Please share, it would be a nice conclusion.

P

Here is how I did it…

Parent table is called Metrics. Child table is called Metrics Tracking.

In Metrics Tracking I added 3 columns:

  1. Metric Name: formula - thisRow.Metric.Name
  2. Metric Rank: formula - rank(thisRow.Date,thisTable.Date)
  3. last n by Metric: formula - Filter(thisTable, Metric=thisRow.Metric)
    .[Metric Rank].Sort(true()).Slice(1, [Sample Size]).ReverseList()

This last column creates a list of the last n rows ordered by date.

I have a control called Sample Size where I can change the sample size dynamically.

On the parent Metrics table, I added these columns:

  1. Last n Metrics Tracking: formula - thisRow.[Metrics Tracking].Filter(Metric.Name=thisRow.Name).[Last n by Metric].First()

This formula copies the ordered list from Metrics Tracking.Last n by Metric to the Metrics table

  1. Last n Values: formula - thisRow.[Metrics Tracking].Filter(And(Metric.Contains(thisRow),In([Metric Rank],thisRow.[Last n Metrics Tracking]))).Sort(True(),[Metrics Tracking].Date).Value

This formula copies the values from the Metrics Tracking table, based on the previous column.

  1. MR Values: formula - Sequence(1,thisRow.[Last n Values].Count()-1).FormulaMap(abs(thisRow.[Last n Values].Nth(CurrentValue+1) - thisRow.[Last n Values].Nth(CurrentValue)))

This formula creates the moving range between each value in the above column (value 1 - value 2, value 2 - value 3,…)

Now that we have the MR values, we can calculate the average moving range, or anything else.

Here is the link to the doc.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.