How do I get a cumulative amount using running sum in building balance sheet

Question & Answer
, , , , ,
1

Good Day!

I am building a balance sheet report for one of our client. But I don’t get the correct amounts that should reflect in my pivot table.

Goal: Cumulative amounts per account group and account type based on selected end date control

These are the correct amounts, but no date filter set up yet. I used ifelse and running sum functions to get the correct values:

Bank and C*sh Accounts =
2024 - 1,670,003
2023 - 524,445
2022 - 1,599,284

(1) IFELSE =
ifelse({Account Code}=‘10101010’
OR {Account Code}=‘10101020’
OR {Account Code}=‘10103010’
OR {Account Code}=‘10103020’
OR {Account Code}=‘10104010’
OR {Account Code}=‘10105010’
,{Move Line Debit}-{Move Line Credit},0)

(2) RUNNING SUM =
runningSum
(
sum({Bank and C*sh Accounts}),
[truncDate(“YYYY”,{Move Date}) ASC]
)

But when I do the computation for overall balance by account group and account type, amounts are incorrect:

BS Value =
ifelse(({BS Group}=‘A. ASSETS’),

{Move Line Debit}-{Move Line Credit},

{Move Line Credit}-{Move Line Debit})

BS Value (Running Sum) =
runningSum
(
sum({BS Value}),
[truncDate(“YYYY”,{Move Date}) ASC]
)

image
image1302×548 82.8 KB

Thank you!

2

Hello @aguasivyjeane , welcome to the QuickSight community!

Can you put this into an Arena? I think that will be easier to troubleshoot your calculated fields to see where the issue might be coming from. I have a hunch that it has to do with your second calculated field.

Also, you mentioned creating a control parameter to apply an end date, did you create that to replace {Move Date}?

2 Likes
3

Hello @aguasivyjeane !

It has been some time since we have heard from you so this topic is being archived.

If you still need help please feel free to post a new question at the top of the community so that your topic is a priority for the community experts.