Calculate Percentage without Empty Fields (1 Viewer)

Pop_Access

Member
Local time
Today, 02:36
Joined
Aug 19, 2019
Messages
66
Hi All,

How I can calculate the percentage for a numeric field in report with the following conditions:

1- field is not empty (Contains value).
2- if the field value >= 4.5

For example:-

Mark
10
0
Afgf
7
3

the percentage should be (2 (10 and 7 >=4.5)) / (4 (not 5)) = 50%
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:36
Joined
Oct 29, 2018
Messages
21,357
Hi. What is "Afgf?" If it's a numeric field, why does it contain a text value?
 

Eugene-LS

Registered User.
Local time
Today, 12:36
Joined
Dec 7, 2018
Messages
481
May be IIf function ...
=IIF([Your field name]>=4.5; [Your field name]/4; 0)
 

Eugene-LS

Registered User.
Local time
Today, 12:36
Joined
Dec 7, 2018
Messages
481
but the number of fields is not fixed
I think this is solvable, besides you can use IIF inside another IIF ...
BTW - The longest IIF expression I've seen:
Analysis_Result: IIf([COMPLETION DATE] IS NOT NULL,IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C in App4.",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))),IIf([tbl_CMS_WO_2_Full].[STATUS]<=10 OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "98" OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "99","WO not yet implemented",IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C Test Date in App4. Additional investigation",IIf([COMPLETION DATE] IS NULL AND [COMPLETED/COMPLETION DATE] IS NULL,"",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))))))
believe me - I didn't write it :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:36
Joined
Oct 29, 2018
Messages
21,357
sorry; this field should be empty, but to keep the row, I wrote the text "Afgf" with white color
Okay, thanks for the clarification. You could try using DCount(). For example.

DCount("*", "TableName", "FieldName>=4.5") / DCount("*", "TableName", "FieldName Is Not Null")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:36
Joined
May 7, 2009
Messages
19,169
you create 3 queries:
1st query (query1):

SELECT Sum(yourTable.Mark) AS TotalMarks
FROM yourTable
HAVING (((Sum(yourTable.Mark))>4.5));

2nd query (query2)

SELECT Count(yourTable.Mark) AS Cnt
FROM yourTable;


the Final query (average query):

SELECT DLookUp("TotalMarks","Query1")/DLookUp("Cnt","Query2") AS Average
FROM yourTable
GROUP BY DLookUp("TotalMarks","Query1")/DLookUp("Cnt","Query2");
 

Pop_Access

Member
Local time
Today, 02:36
Joined
Aug 19, 2019
Messages
66
I think this is solvable, besides you can use IIF inside another IIF ...
BTW - The longest IIF expression I've seen:
Analysis_Result: IIf([COMPLETION DATE] IS NOT NULL,IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C in App4.",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))),IIf([tbl_CMS_WO_2_Full].[STATUS]<=10 OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "98" OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "99","WO not yet implemented",IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C Test Date in App4. Additional investigation",IIf([COMPLETION DATE] IS NULL AND [COMPLETED/COMPLETION DATE] IS NULL,"",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))))))
believe me - I didn't write it :)
I really believe you ;)
 

Pop_Access

Member
Local time
Today, 02:36
Joined
Aug 19, 2019
Messages
66
I would like to share with you what I did, I wrote the following code in unbound textbox in the report footer:-

Code:
=FormatNumber(Sum(Abs([Evaluation]>=4.5))/Count(*)*100,2)+" %"

but unfortunately, I can not exclude the empty records if any in [Evaluation] field as I want.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:36
Joined
May 7, 2009
Messages
19,169
replace Count(*) with Count([Evaluation])
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 19, 2002
Messages
42,970
If your amounts default to null, the Avg() function works correctly since it ignores null. So if you have three rows with the following values,
3, null, 3 --- the average is 3
If the default is 0, then the average for
3, 0, 3 --- is 2

So,
Select Avg(score) as avgscore
From Your table;

Will always be correct if your default is null. If your default is 0, I'm not sure how you would know which 0 is an earned 0 vs a not entered yet value.

If you are averaging named columns in a table - you need to take off your Excel hat and normalize the table. There are no functions that will perform this calculation for you in a relational database because the structure violates first normal form. You can do it, but you need to write it yourself and you need to figure out what the denominator is based on the number of null values.
 

Pop_Access

Member
Local time
Today, 02:36
Joined
Aug 19, 2019
Messages
66
If your amounts default to null, the Avg() function works correctly since it ignores null. So if you have three rows with the following values,
3, null, 3 --- the average is 3
If the default is 0, then the average for
3, 0, 3 --- is 2

So,
Select Avg(score) as avgscore
From Your table;

Will always be correct if your default is null. If your default is 0, I'm not sure how you would know which 0 is an earned 0 vs a not entered yet value.

If you are averaging named columns in a table - you need to take off your Excel hat and normalize the table. There are no functions that will perform this calculation for you in a relational database because the structure violates first normal form. You can do it, but you need to write it yourself and you need to figure out what the denominator is based on the number of null values.
Thank you for your Idea. the problem has been solved by using the following formula:-
Code:
=FormatNumber(Sum(Abs([Evaluation]>=4.5))/Count(Evaluation)*100,2)+" %"

Thanks for All.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 19, 2002
Messages
42,970
Count(fieldname) counts only rows with non-null values in fieldname. Count(*) counts all rows in recordset which seems to be what you want.

[Evaluation]>=4.5 returns either 0 or -1 so that is what you are summing.

+ is the arithmetic operator. It can, under certain circumstances, be used as a concatenation operator but it has different properties. Unless you understand them and are specifically trying to take advantage of how + works with nulls when you are concatenating (which you are not), stick to the mundane &.

Not sure why you are multiplying by 100.2 though
 

Pop_Access

Member
Local time
Today, 02:36
Joined
Aug 19, 2019
Messages
66
Count(fieldname) counts only rows with non-null values in fieldname. Count(*) counts all rows in recordset which seems to be what you want.

[Evaluation]>=4.5 returns either 0 or -1 so that is what you are summing.

+ is the arithmetic operator. It can, under certain circumstances, be used as a concatenation operator but it has different properties. Unless you understand them and are specifically trying to take advantage of how + works with nulls when you are concatenating (which you are not), stick to the mundane &.

Not sure why you are multiplying by 100.2 though
Thank you for your advice regarding using "&" instead of "+"
I multiply by 100 to display the result before the comma, then I used ",2" to display only two fractions.
 

Users who are viewing this thread

Top Bottom