Split Batch Payment by Individuals In Batch

[DrB1]

I have the following registration data that includes a Registration Batch id and distinct Individual id's registered in that batch. The Amount Paid is a batch amount, and is duplicated in any table I produce with the individuals. I want to split the batch payment to divide equally to each individual. For example if Batch 1 paid $100 for 2 individuals, I would put $50 on each individual's amount paid (vs the full $100 for each individual).  I cannot think of anyway to split the payment in QlikView or Qlik Sense without having to export to excel to create a calculated column. Can you help me?

Here's my load script:

LOAD
RegistrationBatchId,
IndividualId,

FirstName,
LastName,
BirthDate,
TotalCost,

AmountPaid,
Balance

[QFabian]

Hi @DrB1 , Something like this ?

Split:
Load
   RegistrationBatchId,
   (sum(AmountPaid) / count(IndividualId)) / count(IndividualId) as Amount_Split
From [Your Source]
Group By
   RegistrationBatchId;

[DrB1 · Accepted solution]

@QFabian  yes! It works. Thank you!!!   Help me understand the math so I can do this again in the future - it looks like the batch sum is being divided twice by the individual count, which doesn't work in my head. 100/2=50 50/2=25.....but it comes out 50. Why?

[QFabian]

each row has 100, there are 2 rows, so it is 200, and then divided by 2, 100 again, and then, divided by 2.

[DrB1 · Accepted solution]

Got it! Thanks so much!