Quantity and Date Range Calculations

Mark01
Mark01 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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?

Author

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”)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
I tried using mlmcc's formulas, but the average is not correct. The report is attached. Here are some screenshots.
Report

Author

Commented:
Here is the report file.
Daily_Dosage2.rpt
Commented:
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

Author

Commented:
Thank you, mlmcc and Raghavendra.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial