Tables joining and running totals calculation

Question & Answer
,
1

Hi team

I am new in Quicksight and trying to explore it’s capabilities.
Currently i am checking the options of multiple tables joins in SPICE and making the cumulative calculation based on the following tables input:

  • Actuals transactions table
  • Forecast transactions table
  • Balance values

I’ve visualized by business case which i manually calculated in the excel file and curious if the highlighted calculations in blue are possible in QuickSight analysis

image
image1920×1033 226 KB

So the case is the following:

  1. Outer join actuals and forecast transaction tables in 1 view
  2. Join the start balance values for both actuals and forecast very start dates
  3. Calculated the running totals of both actuals and forecast transaction amounts including their start balances

I would appreciate all recommendations on how to make this happen! Let me know if any further details or clarifications would help
Thanks

2

Hi @AndriyShepel

Welcome to the QuickSight community!

If you have data in Excel or CSV files for Actual, Forecast, and Balance information, you can easily import these files into QuickSight as datasets and use the Join Data option to combine them for analysis.

If your data is stored in a database, you can create a Custom SQL Query or use the Join Data feature in QuickSight to create a dataset that combines your Actuals, Forecast, and Balance tables.

Once the data is joined, you can create a calculated field for the Running Actuals and Running Forecast during the analysis phase to track cumulative values.

Please refer to the below documentation this might be helpful for you.

3

Hi @AndriyShepel

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with closing this topic.

Thank you!

4

Hi @AndriyShepel,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!