Hello Zoho Team,

I am trying to replicate Zoho Books' "Customer Balance" report into analytics using Query Tables. I have written queries to achieve the expected result but have not been able to replicate it fully.

3 queries I wrote contain: 

1. Query Q1 lists all Open and Overdue Invoices with Available Credits and Unused Amounts

2. Query Q2 lists all Closed Invoices with Available Credits and Unused Amounts

3. Query Q3 I am merging both queries above with a condition only to fetch closed invoices where Available Credit or Unused Amount is not 0.

I am able to achieve 95% correct results. But some customers have Invoices both closed and Overdue with Available Credit and Unused Amounts. In that case, when I am building a summary report it is summing up the same value and showing incorrect results.

I am looking for help or if anyone can guide me on how to replicate this report.

Thanks & Regards
Bhoomi Joshi