Quantity and Date Range Calculations

I am working on a formula that calculates the number of milligrams of aspirin that a patient consumes daily. A patient consumes varying amounts of aspirin daily. A blood test determines the concentration of aspirin in the patient. The test results are always fluctuating.

Here is some sample data:
Patient takes the following quantity of aspirin from January 1, 2018 to January 3, 2018: 1-1-18: 1 mg of aspirin;
1-2-18: 2 mg and 1-3-18: 3 mg.

Calculation: 6 divided by 3 = 2 mg in 3 day period. Result is 2 mg per day.

Here is the formula that I am trying to work with:
sum({Aspirin_Dosage.Dosage},{Test_Date.TestDate},”1/01/2018, 1/02/2018, 1/02/2018”)/count({Aspirin_Dosage.
Dosage},{Test_Date.TestDate}, ”1/01/2018, 1/02/2018, 1/02/2018”)

Database fields (Access):
{Test_Date.TestDate} = Need 3 days.
{Aspirin_Dosage.Dosage} = Need to add daily dose in mg for 3 days.

Report = The attached report is grouped on the {Test_Date.TestDate} field and “for each day” is selected under “the section will be printed” option under “Options”.

Screenshots of the database tables are shown below. How do I write a formula that performs the calculations?
 TablesDaily_Dosage2.rpt
Mark01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:
Hi,

How will you ensure you are calculating the average for 3 days only? Are you filtering the data for a particular period and want to get average for that period?
0
Mark01Author Commented:
I want to use a formula to calculate the average for 3 days. Is that possible? I am not doing any filtering.

Can the dates be specified in a formula like the following:

sum({Aspirin_Dosage.Dosage},{Test_Date.TestDate},”1/01/2018, 1/02/2018, 1/02/2018”)/count({Aspirin_Dosage.
Dosage},{Test_Date.TestDate}, ”1/01/2018, 1/02/2018, 1/02/2018”)
0
mlmccCommented:
No.  BUt you can do this


Add a formula

Name - SelectedDosages
If {Test_Date.TestDate} >= Date(2018,1,1) AND {Test_Date.TestDate} >= Date(2018,1,1) Then
    {Aspirin_Dosage.Dosage}
Else
     0

Open in new window


Add a second formula
Name - CountDosages
If {Test_Date.TestDate} >= Date(2018,1,1) AND {Test_Date.TestDate} >= Date(2018,1,1) Then
    1
Else
     0

Open in new window


Your average is then
If  Sum({CountDosages}) > 0 then
    Sum({@SelectedDosages}) / Sum({CountDosages})
Else
    0

Open in new window


mlmcc
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Mark01Author Commented:
I tried using mlmcc's formulas, but the average is not correct. The report is attached. Here are some screenshots.
Report
0
Mark01Author Commented:
Here is the report file.
Daily_Dosage2.rpt
0
Raghavendra HullurSoftware DeveloperCommented:
I have modified your report and attached with this.

The average calculation you are looking for should be for a range of dates and should be displayed as a single value, not for each of those dates displayed, right?
Here is a modified version of your report with newly included values appearing just below the 3 headers you have used for CountDosages, SelectedDosages and AverageDosages.

I have created 3 new formulae and is also accepting a From Date and To Date parameters and the values are calculated based on those.
The 3 newly created formulae starts with test... under "Formula Fields".

In case, you doesn't need parameters, you need to hardcode the date range just like you did in other formulae.

Hope that helps.
Daily_Dosage2--1-.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark01Author Commented:
Thank you, mlmcc and Raghavendra.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.