Scientific Fomula help please CR10

[LIMS2LIMS]

I have a field called PA_NAME, it has all the test names in it, another field is SRESULT, it has the results in it. I have to develop a formula that pulls only 'Pct Fat'and 'Pct Moisture' then calculate it like this 100-Pct fat=x,Pct moisture/x= answer.

the second one is 100-Pct moisture=x, Pct fat/x=answer

this is per another field 'Sample ID' which is the group

Please help.

 

[midearth]

Hmmm...those LIMS fields look rather familiar. First of all, if I'm not mistaken the SRESULT field indicates string type so you'll have to verify that (look to see if you have an NRESULT field which would be the numeric equivalent).

This will require multiple running totals to accomplish.

RTotal1 criteria:
Summarize NRESULT field using Maximum as the type of summary; Evaluate based on formula (using x+ 2 button) and enter PA_NAME = "Pct Moisture"; Reset on change of group Sample ID.

RTotal2 criteria: Do the same thing but change the evaluation to PA_NAME = "Pct Fat"

Once those are done, create two formulas to be placed in your report as:

Formula1
numbervar x:=(100 - RTotal1);
numbervar y:=RTotal2/x;
y

Formula2
numbervar x:=(100-RTotal2);
numbervar y:=RTotal1/x;
y

 

[LIMS2LIMS]

Thanks Midearth, (You have my curiousity.)
Anyhow you also have me lost at the statement "evaluate based on formula" I'm not clear.

Thanks

 

[midearth]

Sorry I didn't catch you were on v.10. I'm running v. 9 and it may be slightly different.

There are three steps in a running total. First is selecting the field to summarize and the type of summary to conduct. Second part is the evaluation criteria. One choice for evaluation criteria is to evaluate based on a formula. Selecting this will allow you to enter a formula using the x+2 button where you would enter the parameter name (assuming we might be on the same LIMS systems) would be:

{SAMPLEPARAM.PA_NAME} = "Pct Moisture

 

[LIMS2LIMS]

WinLIMS ??

Sorry for my lack of understanding, but I do not see an option for evaluation when seleting the field I've just summarized. Is it in the section expert?

 

[midearth]

WinLIMS it is.

It appears as if you're creating summary fields instead of using Running total. You'll have to use running total to accomplish this. In the "Field Explorer" options, there is an option for creating a running total. This will open a pop-up box (they get named automatically by default) where you enter the criteria for the running total which are the three steps I've mentioned earlier.

If you'd rather handle this outside of the forums and don't mind sending e-mail info, we can do it that way.

 

[LIMS2LIMS]

how can I give you my email without publicizing it? It would be great to have another lifeline. Hint, I was the one that tried starting the most recent Winners group

 

[lbass]

You don't necessarily have to use running totals to do this, but you should show some sample data. Your post isn't very clear about report structure or about what you are trying to calculate.

Also please note that site policy is to keep exchanges within the forum and NOT to e-mail each other.

-LB

 

[midearth]

Understood. I think I know where we're going with this.

 

[midearth]

Sorry about that LB. That was my bad and I hit submit before thinking and shouldn't have recommended taking outside of this forum. I've got a report linked to the exact same database LIMS2 is using which is why it's kinda frustrating trying to explain w/o screen shots. Sorry.

I'll let LIMS2 take over from here to provide sample data to share.

 

[LIMS2LIMS]

Thanks Midearth for your help, I was able to get the formula to work. You were right, I was craeting a summary field instead of a running total.

LB,
I will not deviate from the forum rules, however if you review the conversation between Midearth and myself you will see that we share the same database. My company does not permit me to share sensitive results, however the initial request does explain my delema. Sorry for the miscomm
If you know of a better/easier way I would like to here it.

Thank you,

 

[lbass]

You should be able to supply some example data without compromising security by making up names for example. I don't really follow what you mean by your formulas. Let's say that for:
moisture fat
Sample 1 5 3
3 2
6 7
14 12

For the first row, would you expect to see a %moisture of 5/12 and a fat% of 3/14?

If so, then you could use formulas like:

//{@Moisture}:
if {table.PAName} = "Moisture" then {table.SRResult}

//{@Fat}:
if {table.PAName} = "Fat" then {table.SRResult}

Right click on each of these and insert sums at the group level. Then create formulas like the following:

{@Moisture}/sum({@Fat},{table.sample})

{@Fat/sum({@Moisture},{table.sample})

...and place them in the detail section. Not at all sure this is what you meant though...

-LB