Jump to content
  • 0

Calculating Installment Payments


Question

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 

 

Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...