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.
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. 
This eliminates the need for the formula on the Value column in Temp storage.
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:
- Metric Name: formula - thisRow.Metric.Name
- Metric Rank: formula - rank(thisRow.Date,thisTable.Date)
- 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:
- 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
- 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.
- 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.