Forum Discussion

Eugene's avatar
Eugene
Qrew Member
2 days ago

Running totals for charts

Hi all

Want to share my solution on how to create running totals for charts. QB does not support running totals for charts, only summary reports.

QB Junkie has a really good solution to create running totals using the query formula but as stated in her video it only works with small data sets and it is really memory intensive.

QB has posted a solution to use the report API function to build a new running totals table from a summary report but I found this to be even slower and it does not give the user live updates.

I use own table relationships that allows me to chart large datasets quickly and live. Below is an example of this application but I use this for many other data sets that do not use dates so do not get stuck on the dates table step.

  1. Create a dates table.
  2. I made the key a text field so that i can create the reference field. Record ID is also an option but a bit harder. My key was "<date>-<project no>"
  3. Create an own table relationship
  4. I use pipeline's Make Request action to create and maintain this table with its key and related field value. This is a Jinja code on Exchange I borrowed so let me know if this is of interest to anyone. It super-fast but again not a requirement.
  5. Summarise the values I want into the dates table
  6. Create a blank formula field and this will be my running total
  7. Summarise the blank formula field i created in step 6 above. I use the own table relationship for this.
  8. Go back to the blank formula field in step 6 and write a formula that adds the summarised value in step 5 to the summarised value in step 7. BUT I found this needs to be via an if statement and can't be a simple addition as it fails randomly. Maybe a bug in QB but I found my way around this and its now super reliable. My if looks like this

If( Nz([Field in step 5]) + Nz([Field in step 7]) <> 0,

Nz([Field in step 5]) + Nz([Field in step 7]),

Nz([Field in step 5]) <> 0,

Nz([Field in step 5]),

Nz([Field in step 7]) <> 0,

Nz([Field in step 7]))

Thats it. Fast reliable running totals to draw charts! See screenshot below

reports