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

  • 1
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
  • (Edited)
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.
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb

Posted 3 months ago

  • 1
  • 1
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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])))