Sum data from several tables based on criterias

Ask for Help
1

Hello,

I know that this topic has already been discussed but I’ve read various posts and didn’t find/understand a solution that I could apply :frowning:

I’ve 2 tables

  • A table where I input the mm of rain fell per date. In this table, the main fields are RowID, rainDate, mmRainFell and a Math field to compute the weeknumber (I could have multiple entries for the rainDate).
  • A table where I input the mm of water spread over an area per date. In this table, the main fields are rowID, areaID, mmIrrigatedWater, irrigationDate and again a math field to compute the weeknumber (I could have multiple entries for the same irrigationDate and areaID).

I would like to display in a screen the total mm of rain fell + total mm of water spread over an area per week. I struggle how to put together the relation, math and if-then-else fields that seem necessary to use as read in other posts.

Thank you for your help,

Renauld

2

As long as you have a table where you store unique weeks, I think you can create relations using the computed weekNumber (make a math column in the weeks table), then rollup the related sum mm of rain fell, and another sum rollup for mm of water spread.

I assume you want two lines, so your chart would look at the weeks table, then plot the two rollups as 2 different lines, X-axis being the week text (something that is readable like Week starting on Date).

1 Like
3

Thank you for your answer.

For the rain per week, it’s ok now. I’ve created a Weeks table with a relation to the Rain table and rollup field

About the mm of water spread over an area per date, I still struggle somehow.
My problem is that I’ve to sum up the mm of water irrigated per week AND per area.
The final result should be

  • Week 17
  • → Area 1 - 12mm
  • → Area 2 - 5mm
  • → Area 3 - 0mm
  • Week 18
  • → Area 1 - 6mm
  • → Area 2 - 0mm
  • → Area 3 - 2mm

Thank you

4

Question: Do you need to be able to view all weeks for all areas at the same time? Or would it be okay to first select a week, and then see the aggregated data for each area? (or vice versa)

1 Like
5

It’s ok if I select a week and I see the aggregated data for each area

6

Okay, so my suggestion:

  • Create a table that lists all your Areas in a single column (if you don’t already have one)
  • When you select a week, write the value into a User Specific column in your User Profile row.
  • In your Areas table, create a Query column, target it at your data table, and apply the following filters:
    – Week is User Profile Row → Selected Week
    – Area is This row → Area
  • Then you can create a rollup through that Query column to aggregate the data for each Area
1 Like
7

Great
It works indeed

Thank you

1 Like