# Quantity and Date Range Calculations

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?
Daily_Dosage2.rpt
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer

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?

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”)
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
No.  BUt you can do this

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

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

``````If  Sum({CountDosages}) > 0 then
Sum({@SelectedDosages}) / Sum({CountDosages})
Else
0
``````

mlmcc

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

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

Commented:
Thank you, mlmcc and Raghavendra.

Do more with