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, 20187 yr by WF7A
Spelling police

Hello,

Could you display in browse mode please ?

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

Tom

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. 

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

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.)

Hey,

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

How it is calculate ?

Tom

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

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

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 

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

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

*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.

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

 

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.

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