Calc for summarizing averages in a summarized report

[Rich S]

I'm racking my brain with this one.

I put together a summarizing report (sub-summarized by date range then test code (i.e., 5002, 5003, 5004, 5005) but where I'm getting hung up is in the last two columns, % Above Passing (score) and % Below Passing (score). As you can see, the numbers repeat themselves for each date range sub-summary...and for some strange reason, the 5003 averages are identical to the 5002 averages, but they shouldn't be.

To calculate the % Above Passing (score) and % Below Passing (score) numbers, respectively, are these calculated number fields:

PercentAbovePassAvg: ( ( AvgPass_ScoreAbovePassing__lxs - TEST_CODE::MinPassingScore__lxn ) / TEST_CODE::MinPassingScore__lxn ) * 100

...and...

PercentBelowPassAvg: ( ( TEST_CODE::MinPassingScore__lxn - AvgFail_ScoreBelowPassing__lxs ) / TEST_CODE::MinPassingScore__lxn ) * 100

 

I suspect that I need a Summary field but the only radio button choice I can think of to use is Average each of those calculated number fields but that doesn't work (at least the way I have it coded).

 

So, my questions are: How/Do I set up a Summary field, if needed, to determine each test code's averages for above and below the Minimum Passing Score, and how do I prevent the numbers from repeating themselves in each data range'd subsummary?

 

Cheers,

Rich

 

Printout1.pdf

Edited August 29, 2018 by WF7A
Spelling police

[Tom Assia]

Hello,

Could you display in browse mode please ?

if you display without summary, only list all element did the calculation works ?

Tom

[Rich]

Hi, Tom:

Attached is the result in Browse mode for Test Code 5002 spanning over two years. The %Above Passing and %Below Passing numbers are correct. However, when I run the scripted report where it sorts by Date Range then Test Code, those two columns' numbers are incorrect.

I'm afraid I don't understand your second question. 

[Tom Assia]

Hello,

My second question was without sort did the calculation is correct ?

I see in the first windows 8/1/2015...1/31/2016

in the second 8/1/2016...7/31/2017 is it normal ?

Tom

[Rich]

I narrowed the found set(s) and used a different date range in the second upload; my apologies for not noting that for you.

...and yes, without sorting the calculation is correct in the latter examples so it's either the calculation isn't correct when used for sorting/subsummarizing, or the part set-up needs tweaking. (For grins, I changed the summarizing window pop-up menu from All to Individually--it had no effect.)

[Tom Assia]

Hey,

I think the problem is on the field AVG  (164 in the second picture)

How it is calculate ?

Tom

[Rich]

It's a Summary field that averages the TestScore field.

[Tom Assia]

is it possible to see all the records maybe in private ? with date, test code and test_score ?

[Rich]

I'll export the data into a new file and will set up the calculations in it--since it's an encrypted file and has student records in it, you can't look at it as-is; I'll then post a link for its access this afternoon after I'm done attending a couple of upcoming meetings.

Thank you VERY much for your ongoing help!

Cheers,

Rich 

[Rich]

Here you go. The account name/password are both admin.

Praxis2.fmp12.zip

Included is the script to print the report--it's fired by the Report button on the PRAXIS_Child layout; a popover appears where you'll enter the dates in the global fields provided. (I included a screenshot of the typical date ranges we use; I've included records that fit within those shown ranges.)

 

Solve this one and a coffee card awaits! : )

Cheers,

Rich

[Tom Assia]

Hey,

I think there is one enhancement in this app .

You want tou make calculation on the pass or fail test. So you have to use another table to focus on success or fail.

I add a table with a link (constant 1) which represent success or not (changing the definition of pass_lcn)

Have a look at this red field

Tom

Praxis2.fmp12

[Rich]

*smh* I never would've thought of using another table with multi-keys. Brilliant! I added another T.O. (PRAXIS_CHILD_FAIL) that mirrors the one you created to obtain the % Below Passing scores. (If you'll private message me your e-mail address I'll send that coffee card.)

What I'm having trouble wrapping my head around is the Constant field--I've used something similar to it before to tie T.O.s together but by using a calculated value of 1; the one in your fix is just an uncalculated number field, so how does that field "work"? In addition, I noticed that in the added T.O.s (with Constant as one of the keys), irrespective of whether I use a > or < sign with its connection to Report_Avg_Score_Pass__lxs (or Report_Avg_Score_Fail__lxs), it doesn't change the output's value.

[Tom Assia]

Hey,

Thanks you coffee card , just put a like !

you cannot use a calculated field for this link, use to auto entry value or constant and in your script set replace content by 1.

So how is it know works ?

Tom

 

[Rich]

It appears to be working fine, but I don't follow about inserting a script step to replace Constant's content by one; where should I insert that?

I discovered that I had to tweak the far right ends of the calculation formulas for Report_Pass__lcn and Report_Fail__lcn to null instead of zero:

Case ( not IsEmpty ( TestScore__lxn ) and TestScore__lxn ≥ TestMinPassingScore__lcn ; TestScore__lxn ; "" )

If zero is used it throws the count off.

[Tom Assia]

You're right, better to set 0 in place of null value.

In your script run -> display template -> display all records -> replace constant by value you want

Tom