Charting or displaying summaries of data with multiple conditions

#1

I am trying to summarize data that I can display in a chart.

I have a table of projects with a column for clients assigned to the projects. Two separate payments are made to each client for each product. I am tracking whether the payment has been “paid” or is “outstanding” as well as the fiscal year the payment was or is expected to be paid. So the relevant columns in the table are:

  • Client
  • Payment 1 Amount ($ value)
  • Payment 1 Status (“paid” or “outstanding”)
  • Payment 1 Year (2010, 2011, 2012, etc)
  • Payment 2 Amount ($ value)
  • Payment 2 Status (“paid” or “outstanding”)
  • Payment 2 Year (2010, 2011, 2012, etc)

I’d like to create a summary table and/or chart that shows paid vs outstanding combined payment amounts for each fiscal year. For example, I would easily see that so far in 2018, I have paid $17,000 and there are $25,000 outstanding payments. And for 2019, I’m expecting $40,000 in total payments etc.

The main challenge I’m having is that I cannot simply combine payment 1 with payment 2 into a new column because it’s possible that payment 1 is “paid” while 2 is still “outstanding” and it’s also possible that within a given project, the two payments could correspond to different fiscal years.

I have created formula columns for each fiscal year that totals the paid and outstanding amounts for each client. This gives me the numbers I need at the bottom of the view in the automatic summary row, but it involved creating tons of additional columns - 2 for each year (which is fine) - but I can’t seem to find a way to show that info in a pivot table, matrics, or chart easily since it’s spread out between columns instead of rows.

I hope I’ve explained that well.

Thank you!

0 Likes

#2

Hi @Jonathan_Gunnell - A slight change to the data model might give you what you need. Rather than have payments as columns, I would try treating each payment as a record with statuses of payment1/payment2 and paid/outstanding:

You can then create another view to group and summarise this as you see fit - in the case below by paid status, then by year:

Does that give you what you want?

JB

0 Likes