Discussions

Expand all | Collapse all

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

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

    Posted 08-08-2018 18:09
    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.


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

    Posted 08-09-2018 15:20
    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])))