Forum Discussion

MikeMike's avatar
MikeMike
Qrew Cadet
7 years ago

Summarising 3 groups, adding difference between group 1 and 2 if 1 is larger, otherwise include only group 3

This explanation may be a bit complicated...

I have been asked to produce a pie chart out of Quickbase showing where we are spending our money, per month.  All expenses are generalised down for this chart into two groups.  Typically they are in 8 categories - 3 into group A, 5 into group B.

Category 1 & 2 are expense and income.  They should cancel each other out, leaving just category 3 for group A.  However when category 1 (the expense) is higher than the income in category 2, I need to include this expense difference along with category 3.  If the income is higher, than I must exclude both 1 & 2.

I have some formula fields which I use to get my two groups, based on which category they are in, however this is where I am stuck.

How do I determine, for each month, whether I need to include only category 3, or if not include only the total difference between 1 & 2?

I have tried to mock-up an example showing what I mean...



Currently I'm having to copy all my monthly data to Google Sheets and create my pie charts.  I prefer to have these done in QB as any changes are automatically reflected in the chart.

Is it possible to calculate my values this way?  I don't mind if it's formulas or new tables, I would just prefer to find this info automatically in Quickbase.

Thanks.
Formulas and functions
Quickbase Automations
reports and charts
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    7 years ago
    Here is an example of a way and hopefully I understood this completely and this will help get the charts that you are needing.

    You will need 2 tables - Expense Reporting and Expense Date.

    Expense Reporting will need a field for month and year [Expense Month] because each record will be a single month/year and you will need to make [Expense Month] the key field in Expense Reporting.(you can create a formula text field to show the month without the year if needed).

    Data Expense will need these fields


    Setup a relationship between the 2



    Notice the Summary fields for A1 through A3. If this was full I would have B also. The filtering for the summary fields is pretty simple.


    Here is the Expense Data form I used (not that you need it)



    Now you just need the formula fields over in Expense Reporting to get the following


    [Group A Amount]

    If([A2 Total]>[A1 Total],[A3 Total ],([A3 Total ]+([A1 Total]-[A2 Total])))