Forum Discussion

PaulStreit's avatar
PaulStreit
Qrew Member
5 years ago

Organizing data for graphing

I have an app that totals up billings and costs by calendar quarter by project. There is a Projects table that includes fields for each calendar quarter's total billings and total costs. For example, for 2021 Q1, there would be a field for "2021 Q1 Billings" and another for "2021 Q1 Costs", and a calculated field for "2021 Net Cash", that simply subtracts costs from billings for the quarter. This continues on for up to 10 years worth of quarterly billings and costs. 

My problem is to take this data and plot a cash flow analysis. This would involve plotting cumulative net cash against calendar quarters, ideally in a waterfall chart which I know QB has.

So I have the data but it's broken up into fields for a given project record. I essentially have to go across the project record to build the report, but it looks like QB reports are designed to total vertically across records. I'm looking for suggestions on how to approach this reporting challenge. 

Thank you,
Paul

------------------------------
Paul Streit
------------------------------
Forms
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    So your form has say 40 fields hard coded for a particular quarter for 10 years, so say 40 fields.

    If you want to chart this you will need a process to create child records from those fields, so 40 child records for each project.

    You will need to create a child table and then create a formula URL button which would, for example delete all the child records, and then trigger an Automation which would run 40 saved table to table copies to copy the Projects into the child table and map the correct value into the table along with a field to indicate the YYYYQ. 

    So each time that you wanted to run the Report, you would need to push the button to run that process and then run the report.

    I can help a but more here if you want to tackle this or contract me directly by email for one on one on one assistance.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------