Comment calculer des limits par apport à une donnée de référence par échantillon?

[#1]

Bonjour,

Je fais une étude de stabilité et souhaite mettre des limits à +/- 20% de mon résultat au jour 1. Je sais ajouter les limites au graoh grâce à l'aide d'un autre membre de cette communauté mais maintenant je voudrai mettre en place une formule pour que cette limite soit calculer automatiquement en tenant compte de la valeur au jour 1 pour chaque échantillon. 

J'ai écris cette formule pour la limite supérieure mais qui ne marche pas. 

:Data[Row(), :Day == 1] * 1.2

Je pense que je me trompe dans l'utilisation de l'indice.

Je mets en PJ la table d'exemple que j'utilise avec les résultats que je souhaiterai suite à l'application des formules sur les colonnes USL (valeur à Jour 1 + 20%) et LSL (valeur à Jour 1 - 20%).

Est-ce que quelqu'un a une solution?

Merci d'avance

[#2]

Hi,

For this type of operations, I tend to use a simplified approach that relies on creating a new "Day 1" column with the formula below:

If (Row () == 1, :MEASURE, if (:TIME == "Day 1", :MEASURE, Lag (:DAY_1, 1))

Then it is a simpler procedure to calculate the USL and LSL as a reference for the other time points.

Does that make sense?

Best,

TS

[#3]

Hello,

thanks for your answer but sorry but I don't manage to do it.

I understand that you want to create a new column where I report Day 1 data for all timepoints. But then how do you create this column? I have lot of rows and cannot copy paste each row one by one. If your formulate is to create this row it doesn't work for me. What is you Day_1 variable? Is it the new row name or the name for the limits values?

Best,

Marie

[#4 · Accepted solution]

Hi, 

Sorry for the confusion. Here are the detailed steps to produce the desired result

1. Sort your table by SAMPLE (first) and Day (Second)
2. Add a new column named "DAY_1"
3. Enter the formula below in the new column

If( Row() == 1,
	:Data,
	If( :Day == 1,
		:Data,
		Lag( :DAY_1, 1 )
	)
)

The rest should be relatively easy but don't hesitate to contact me if you have problem creating the USL and LSL from the new DAY_1 column.

Best,

TS

[#5]

Thanks a lot Thierry,

Indeed it is a simplier way!

I didn't know the Lag function so it is where I got lost.

Best,

Marie

[#6]

Here are couple of ways to do this, using Loc() and Eval(Eval Expr()) (a bit similar as here: Re: How to calculate relativ values based on a position of a base value? .

Names Default To Here(1);

dt = New Table("Example_USL_LSL",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Sample",
		Character(1),
		"Nominal",
		Set Values({"A", "A", "A", "A", "A", "B", "B", "B", "B", "B"})
	),
	New Column("Day",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 3, 5, 10, 1, 2, 3, 5, 10])
	),
	New Column("Data",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([10, 12, 9, 7, 5, 160, 158, 162, 67, 26]),
		Set Display Width(46)
	)
);


/*a = Loc(:Sample << get as matrix, :Sample);
b = Loc(:Day[a], 1)[1];
c = a[b];
:Data[c]*1.2; //0.8 for lsl
*/

dt << New Column("LSL", Numeric, Continuous, Formula(
	If(Row() == 1,
		dayOnes = Loc(:Day << Get as Matrix,1);
	);
	dayOneForSample = dayOnes[Loc(:Sample[dayOnes], :Sample)][1]; //current samples day 1 row number
	:Data[dayOneForSample]*0.8;
	)
);


dt << New Column("USL", Numeric, Continuous, Formula(
	If(Row() == 1,
		dayOnes = Loc(:Day << Get as Matrix,1);
	);
	dayOneForSample = dayOnes[Loc(:Sample[dayOnes], :Sample)][1]; //current samples day 1 row number
	:Data[dayOneForSample]*1.2;
	)
);

dt << New Column("LSL_eval", Numeric,"Continuous",Format("Best", 12), Formula(
	Name Expr(
		Eval(
			Eval Expr(
				:Data[(:Sample << get data table) << get rows where(
					:Day == 1 & :Sample == Expr(:Sample[Row()])
				)] * 0.8
			)
		)
	)
));

dt << New Column("USL_eval", Numeric,"Continuous",Format("Best", 12), Formula(
	Name Expr(
		Eval(
			Eval Expr(
				:Data[(:Sample << get data table) << get rows where(
					:Day == 1 & :Sample == Expr(:Sample[Row()])
				)] * 1.2
			)
		)
	)
));

If you were to use Loc() solution I would strongly suggest checking out it on smaller parts to understand what is going on.

[#7]

Hello Jarmo,

Thanks for this answer. It looks harder than the other one proposed to me so I will stick to the simplier one but still interesting to know these functions for other cases.

Best,

Marie