Discussions

Expand all | Collapse all

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

Casameira Reyes06-21-2017 21:14

Casameira Reyes06-21-2017 21:16

QuickBaseCoach Dev./Training06-21-2017 21:18

Casameira Reyes06-22-2017 13:22

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

    Posted 06-21-2017 20:40
    My formula consist of multiple sections each equaling a percentage; however, when a section Null it isn't calculating in the total scores. 


  • 2.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 20:45
    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.


  • 3.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:02
    It is a formula field. I don't want it to equal zero because it will hurt the score. 


  • 4.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:04
    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.


  • 5.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:08
    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)


  • 6.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:11
    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)


  • 7.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:14
    I had tried this originally and it doesn't work


  • 8.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:15
    What do you mean by "it does not work". Syntax error or incorrect totals?


  • 9.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:16
    I'm sorry I am getting the same result of blank


  • 10.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:18
    Can you post your formula?


  • 11.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:29
    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)


  • 12.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:31
    Is there data in any of those individual fields on the record you are testing on.  It should work.
    Mark


  • 13.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:33
    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))


  • 14.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:35
    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?


  • 15.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:38
    Yes, it is one of the section included in the total. It calculates the percentage of the questions answered. 


  • 16.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-21-2017 21:39
    Satisfactory = 1
    Improvement needed = 0
    Not Applicable doesn't equal anything and shouldn't count in the percentage of that section. 


  • 17.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-22-2017 00:05
    Are you saying that the field [Section Total:] in your test record has a value in it?.


  • 18.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-22-2017 13:22
    Yes, it has a percentage. 


  • 19.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-22-2017 13:26
    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.


  • 20.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-22-2017 13:29
    I have already put in a support case. They said there will have to be a numeric value. 


  • 21.  RE: I am trying to get a percentage omitting a NULL field.

    Posted 06-22-2017 15:09
    I am not seeing your contact information. Can you please email me at cassie.reyes@fedex.com