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 thi...
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])))
