Rollup Challenge

Ask for Help
1

Ok, experts, I need help on something (I think is) complicated:

I have a table of land area reports, and I would like to analyze comparisons. My users submit a report for each piece of land, including the location name, the type of land, the year of the report.

I would like to be able to calculate how the land area changes year-by-year. Here’s an example:

Area 1 / Forest / 2020 - 500 m2
Area 1 / Forest / 2021 - 480 m2
Area 1 / Urban / 2020 - 3 m2
Area 2 / Wetland / 2022 - 23 m2

For this example, I want to calculate 2021 change from previous year on Area1 / Forest = 480 - 500 = -20 m2

Area, land type, year, and measurement are all separate columns.

Any ideas? Is there a way to create a summary table? Or to add fields to this table?

2

You can do this:

  • Add a rowID column
  • Create a query column pointing to that whole table, filter by area is the same as this row’s area and land type is the same as this row’s land type
  • Create a lookup column on top of the query column above to return all matching rowIDs
  • Add a “find element index” column to find the index of this row’s rowID, with the array being the result of the lookup column above
  • Add a “last index” math column, with the formula being “find element index” minus 1.
  • Create another query column, now point to the first query column’s result, and filter by “find element index” equals last index
  • Create a single value > first value column to return the land area from the “last match”
  • Add a math column: This year’s area minus the value taken just above, to get the change from year-to-year
1 Like
3

Oh my gawwwd, thank you @ThinhDinh . This looks like a big weekend project. I will give it a go. Thank you!!

4

Will record a video for you shortly.

5

@meg You can check this.

2 Likes
6

Nicely done :+1:

I must admit I did a bit of a double take when I saw you go for the Excel column (I would have used an if-then-else), but it does make sense to use the Excel column because it’s more efficient (less columns). Maybe I need to get over my allergy to Excel :smiley:

PS. @meg - see, it’s only a 5 min job. You’d better make other plans for the weekend :wink:

2 Likes
7

Haha I don’t really use it that often, to be honest :sweat_smile:

1 Like
8

Thank you @ThinhDinh ! I’m about to give this a go. Quick question - does it matter the order of the entries for the calculation? I can see my users adding 2022 first, then 2021, then 2020. Will it still give the correct result for a given year vs the previous year?

9

Ok, I answered my own question - it very much depends on the row order.

I implemented it exactly as show on the video - the video was amaaaaazingly helpful, so thank you very much!! :pray: :pray: :pray: And it works! :heart_eyes: :heart_eyes: :heart_eyes:Assuming the rows are in the right order.

So, next question, is there an easy way to either sort the row, or to have it calculate the field on the newer row?

10

Ok, you guys are awesome. From watching the video, I got a better understanding of how the different columns work. So I came up with another approach:

First I added a math column to calculate the Previous Year.

Next I created a template column to combine the area and type into a single field. Then I used that column to query the column of the previous year. Then a lookup to the previous area used.

Finally I tried excel but couldn’t get it to work, so I made a column that calculated the difference, and an if-then column to screen only those with change.

What do you think? :smiley:

11

You can use a Query to do this (filter by year is this row > previous year and type is this row > type). Then you have a single value column to get the previous area used.

Can you show me how you configured the Excel column?

12

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.