Conditional Summary Custom field not working for Average

Empower2019
Empower2019
in Software Products

I want to calculate the average area of a number of injections, these injections can differ depending on batch and conditions from one run to the next. I set up a Sample Text CF called CF_Ref and I want only injections where CF_Ref = Ref1 to be considered so my second Peak, Real CF (result set only) becomes:

%.%.SAME.AVE(Area*(EQ(CF_Ref,"Ref1")))

The above will consider all labels, all injections and separate channels where only CF_Ref = Ref1. Weird thing is, it works fine for MAX, MIN and SUM area but when I try AVE, I get a really low result which is obviously not the average area. My tablespace is fine, so I don't know what else it could be. The only processing codes I get is SC02 and SC30 which relate to "summary custom calculation error" which isn't very helpful.

Any ideas??

Best Answer

  • EmprowessSeeker
    EmprowessSeeker
    Answer ✓
    While zero is still being included in the calculation, since the average function is replaced by summation, adding in a zero (or zeroes) won't alter the end result. Using the example I gave yesterday, I added the output of both halves of the equation to demonstrate how it should work. 

Answers

  • shaunwat
    shaunwat
    I've always had problems with nested custom fields like what you are using. I would break it into pieces:

    1. CF_Ref remains unchanged.

    2. CF_Ref_Test (Boolean) = EQ(CF_Ref,"Ref1"); true=1; false =0.

    3. CF_RefTest2 = CF_Ref_Ttest*Area

    4. Result = %.%.SAME.AVE(CF_RefTest2)

    Sure, it's double the custom fields, but sometimes breaking things down into smaller pieces is the way to go. 
  • Empower2019
    Empower2019
    Thanks for the reply. However i still get the same incorrect answer when i break it up as you suggested into separate cfs. I think there may be an issue or fault with the AVE function when its incorporated into a nested cf with boolean variables. I tried MIN MAX SUM PROD and RSD on my %.%.SAME.Function(Area*(EQ(CF_Ref,"Ref1"))) and they worked perfect with different placements of Ref1 in the sample set.

    There may be something in the syntax of the cf which doesnt work correctly when asking Empower to do the Average area when only considering Ref1. I have used AVE with no issues on a formula like A.%..AVE(Area) or similar. Maybe as you suggested you need to add an extra argument for the formula to slot into place. Interestingly it works fine when i swap % for SAME and all samples with the same label and where Ref1 is populated gave me the correct answer so this can be my workaround. I could be forever testing which exact boolean or set of booleans in combination trigger AVE to work correctly.

    Its also possible that some CFs that may work in theory just dont work in practice. Thats something i have found a lot. 
  • EmprowessSeeker
    EmprowessSeeker

    Hi,

    I’m not sure if you’re still looking for a resolution to this problem, but I figured it would chime in just in case.

    To the best of my understanding, you’re correct about it being a conflict in syntax.

    %.%.SAME.AVE(Area*(EQ(CF_Ref,"Ref1")))

    Instead of excluding all injections that aren’t tagged with Ref1 from the average calculation, it’s averaging in a zero for any injections not tagged with Ref1. That would be why the result is really low. See attached example.


    If you still need this calculation performed, here’s my recommendation:

    %.%.SAME.SUM(Area*EQ(CF_Ref,"Ref1"))/%.%.SAME.SUM(EQ(CF_Ref,"Ref1"))

    The first half will only sum the peak areas of injections tagged with Ref1, and the second half is essentially a count function.


    Hopefully that helps!

  • medicnman
    medicnman
    Did you flag this with "All or Nothing"?
  • Empower2019
    Empower2019

    Thanks very much for taking the time to reply. That makes sense when you say Empower will include the 0 for all the injections which aren't populated with Ref1. Before I test it though, wont the first half of your formula still give a lower value since 0 is included in the calculation, and then divide this low value by the number of injections when CF = Ref1? Which will give a lower value still?


  • Empower2019
    Empower2019

    Thanks again for your help. I tried that today and it worked out perfectly. Now I have a set of CFs which can include AVE for random injections in Sample Sets. I have a question though, does the % in a CF formula only include rows that do have a label, whatever the label may be? It wont include all the rows with no label?


    Thanks again for taking the time to answer and give your examples, much appreciated!

Categories