I am trying to get a percentage omitting a NULL field.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
My formula consist of multiple sections each equaling a percentage; however, when a section Null it isn't calculating in the total scores. 
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Are you looking to treat a null as a zero?  Can you tell e the type of field you are totaling.  is this a data entry field or a formula field or a summary field.
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
It is a formula field. I don't want it to equal zero because it will hurt the score. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Is this a Total or an Average?  if it is a total, then the zero will not hurt the total as it will be zero.
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Here is the formula: ([Section Total:]* 0.05)+([Section Total]*0.25)+([Unnamed 5]*0.1)+([Unnamed 6]*0.03)+([Unnamed 7]*0.12)+([Unnamed 8]*0.15)+([Unnamed 10]*0.25)+([Section Total;]*0.05)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Try this

nz([Section Total:]* 0.05)
+nz([Section Total]*0.25)
+nz([Unnamed 5]*0.1)
+nz([Unnamed 6]*0.03)
+nz([Unnamed 7]*0.12)
+nz([Unnamed 8]*0.15)
+nz([Unnamed 10]*0.25)
+nz([Section Total;]*0.05)
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
I had tried this originally and it doesn't work
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
What do you mean by "it does not work". Syntax error or incorrect totals?
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
I'm sorry I am getting the same result of blank
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Can you post your formula?
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Nz([Section Total:]* 0.05)
+Nz([Section Total]*0.25)
+Nz([Unnamed 5]*0.1)
+Nz([Unnamed 6]*0.03)
+Nz([Unnamed 7]*0.12)
+Nz([Unnamed 8]*0.15)
+Nz([Unnamed 10]*0.25)
+Nz([Section Total;]*0.05)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Is there data in any of those individual fields on the record you are testing on.  It should work.
Mark
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Yes there is a long complicated formula. See below: 
var number Qone=Case([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qtwo=Case([2.  Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qthree=Case([3.  Has all vehicle and customer supplies available. (see Supply list)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfour=Case([4.  Completes and documents Pre-Trip Vehicle Inspection.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfive=Case([5. Has Vehicle Binder in vehicle.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qsix=Case([6. Does the courier know how often their vehicle should be serviced?], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qseven=Case([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);

If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2.  Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3.  Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4.  Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))>0,
($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven)/(7-
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2.  Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3.  Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4.  Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0))),

If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2.  Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3.  Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4.  Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))=0, 0, 1))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
I assume that is a formula for one of the section fields.  But does that formula calculate to anything for the record that you are testing with?
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Yes, it is one of the section included in the total. It calculates the percentage of the questions answered. 
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Satisfactory = 1
Improvement needed = 0
Not Applicable doesn't equal anything and shouldn't count in the percentage of that section. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Are you saying that the field [Section Total:] in your test record has a value in it?.
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
Yes, it has a percentage. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
I think that you will have to contact support to get them to help you, or else contact me off line via the information in my profile  or website at QuickBaseCoach.com and I will have a quick look myself.
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
I have already put in a support case. They said there will have to be a numeric value. 
Photo of Casameira Reyes

Casameira Reyes

  • 470 Points 250 badge 2x thumb
I am not seeing your contact information. Can you please email me at cassie.reyes@fedex.com