well, I think I found a solution to my problem. Since, at the core, all I really want is for there to be no null values between either of these two fields, I just added a condition to the end of my Switch function that will always be true and assigned it a value of 0. That way anything that satisfies earlier conditions of the switch will be assigned the appropriate value and all others that would've been null due to not satisfying any conditions of the switch are now set to 0. Here are the actual two expressions I'm using
Code:
Completion_1: IIf([status]="pending",Switch([satelliteprovider]="Charter",0.43,[satelliteprovider] In ("centlink","BBQWEST"),0.6,[satelliteprovider]="cableone",0.24,[satelliteprovider]="BH",0.27,[satelliteprovider]="bb8",0.22,[satelliteprovider]="bb7",0.68,[satelliteprovider]="bb6",0.33,[satelliteprovider]="bb4",0.55,[satelliteprovider]="bb2",0.42,[satelliteprovider]="bb",0.44,[satelliteprovider]="mediacom",0.29,[satelliteprovider] Is Not Null,0),Switch([status] In ("chargeback","cancelled","not scheduled"),0,[status]="completed",1))
Code:
Completion_2: Nz(IIf([status]="pending",Switch([satelliteprovider]="wildblue",0.67,[satelliteprovider] In ("direcway","hughesnetf"),0.72,[satelliteprovider]="dish" And [promotion]<>"dishnet" Or [promotion] Is Null,0.85,[satelliteprovider]="dish" And [promotion] Not Like "*dishnet*",0.73,[satelliteprovider]="tw",0.18,[satelliteprovider]="suddenlink",0.24,[satelliteprovider]="Phone3",0.2,[satelliteprovider]="other2",0.29,[satelliteprovider]="insight",0.12,[satelliteprovider]="Frontier",0.6,[satelliteprovider]="el",0.3,[satelliteprovider]="Cox",0.34,[satelliteprovider]="clearwire",0.4),0),0)
Code:
Completion: [Completion_1]+[Completion_2]