Calculate A Percentage

[LWSChad]

Hello;

I am working with a report and aggregate functions.

 

I am trying to get a percentage toward a goal.

 

((COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT]) / (MIN [@pUser_QFGoalWeek]))

Should result in a percentage for completed QuotesFinished to QuotesFinishedPerWeekGoal, but it results in zero.

 

For diagnostic purposes I've checked the following formulas...

 

COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT] = 2 (correct)

 

(MIN [@pUser_QFGoalWeek])) = 10 (correct)

 

((MIN [@pUser_QFGoalWeek]) / (COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT])) = 500.00% (correct)

 

((COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT]) / (MIN [@pUser_QFGoalWeek])) = 0.00% (incorrect)

 

Can anyone spot what I'm doing wrong?

 

Thank You!

[Jan]

Hello EIQ,

 

As far as I know, aggregate functions cannot work with several fields. I mean, COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT]) / (MIN [@pUser_QFGoalWeek] is the same as COUNT_NON_BLANK / MIN and this formula is applied to all fields that are added to the right-hand panel.

 

If I understand correctly, the DataType of your pUser_QFGoalWeek field is Text255 or Integer. If you change the DataType to Number/General, the result should be 500% but note that values of the same field are counted as "non blank".

[LWSChad]

Awesome!

I changed the Goals to General Numbers, the aggregate to COUNT, and used record filtering to account for blanks.

 

Thanks!