Calculating Installment Payments

[bbeshlian]

Hello,

I am trying to calculate total installment payments made to an individual to date. What I was trying to do was nest CASE statements since the SUM function will not work in a Calculated Field. Here is the logic I am using.

If Payment Date 1 is not null then Installment Payment 1 else if Payment Date 2 is not null then Installment Payment 1 + Installment Payment 2 and so on...

When I build my CASE statement it only resolves the first condition. Any thoughts?

Thanks,

Bill 

 

[Alison]

Hi @bbeshlian,

Could you provide me with the code for the Case statements you use?
 

[bbeshlian]

Alison,

Below is the code. Thank you.

CASE

WHEN [@field:Bonus_Data_Table_Installment__Payment_1] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment__Payment_2] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment__Payment_3] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment__Payment_4] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment__Payment_5] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment__Payment_6] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_7] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_8] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_9] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_10] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_11] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_12] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_13] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_14] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_15] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

WHEN [@field:Bonus_Data_Table_Installment_Payment_16] IS NOT NULL THEN [@field:Bonus_Data_Table_Installment_Amount_1]+[@field:Bonus_Data_Table_Installment_Amount_2]+[@field:Bonus_Data_Table_Installment_Amount_3]+[@field:Bonus_Data_Table_Installment_Amount_4]+[@field:Bonus_Data_Table_Installment_Amount_5]+[@field:Bonus_Data_Table_Installment_Amount_6]+
[@field:Bonus_Data_Table_Installment_Amount_7]+
[@field:Bonus_Data_Table_Installment_Amount_8]+[@field:Bonus_Data_Table_Installment_Amount_9]+[@field:Bonus_Data_Table_Installment_Amount_10]+[@field:Bonus_Data_Table_Installment_Amount_11]+[@field:Bonus_Data_Table_Installment_Amount_12]+[@field:Bonus_Data_Table_Installment_Amount_13]+[@field:Bonus_Data_Table_Installment_Amount_14]+
[@field:Bonus_Data_Table_Installment_Amount_15]+
[@field:Bonus_Data_Table_Installment_Amount_16]

ELSE 0.00

END

[douvega]

Hi,

It will resolve the first since it is the first match.

Try inverting the order of the sql statement: so start with the payment_16, then the payment_15 and so on...