Skip to main content

How can I roll up records by month & type, and show aggregated titles in "cells"?

R
Forum|alt.badge.img+3

We have entered a large number of 2018 marketing activities into our new AirTable account. These include the title of the activity, type of activity (e.g. website updates, emailings, conference presentations, etc) and date the activities are intended to take place.

I would like to generate a roll up of these that shows the full year of activities as follows:

  • 12 months as columns (Jan, Feb, Mar, etc)
  • Activity type as rows (Site Updates, Emailings, Presentations, etc)
  • Each “cell” contains a list of the activities in of that type in that month, for example as a comma-delimited aggregate of the activity titles.

This is sort of like a pivot table, but the aggregation shows the actual content (titles) rather than a numeric aggregation (sum or count). We used to do this in Excel, by hand, so I suppose that is technically still an option, but I’m wondering if AirTable can automate this.

Thanks,

Ramon

10 replies

A
Forum|alt.badge.img+3
  • Andrew_Johnson1
  • Participating Frequently
  • 586 replies
  • January 3, 2018

Try using the Group functionality of Airtable, it should help you getting the functionality that you are wanting…
Also try using the Kanban view…Its a pretty neat view that Airatble has incorporated.

A
Forum|alt.badge.img+18

The reply of @Andrew_Johnson1 is the fastest way of a summed result. If you want to achieve an Excel-like view it requires a lot more work.

I think of:
Creating a 2nd table (PIVOT TABLE) and have the primary field contain your type of events (unique names).
In your main table make sure you link your type of events field to the PIVOT TABLE.

In your main table create 12 formula fields with the names of every month. Have something like:
IF(Activity=BLANK(),BLANK(),IF(DATETIME_FORMAT(Date,‘MM’)=1,Activity&", “,”"))
This is for January (Month 1). Do the same for every other column. Of course you may hide all the columns when you’re done.

In the Pivot Table create 12 Roll up fields, and make sure that you CONCATENATE all values. That’s why I added a comma after every activity in the Month field in the Main Table.

Something like this?

A
1 person likes this
  • A
    Alex_Whitton
W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • 1386 replies
  • January 3, 2018
Andre_Zijlstra wrote:

The reply of @Andrew_Johnson1 is the fastest way of a summed result. If you want to achieve an Excel-like view it requires a lot more work.

I think of:
Creating a 2nd table (PIVOT TABLE) and have the primary field contain your type of events (unique names).
In your main table make sure you link your type of events field to the PIVOT TABLE.

In your main table create 12 formula fields with the names of every month. Have something like:
IF(Activity=BLANK(),BLANK(),IF(DATETIME_FORMAT(Date,‘MM’)=1,Activity&", “,”"))
This is for January (Month 1). Do the same for every other column. Of course you may hide all the columns when you’re done.

In the Pivot Table create 12 Roll up fields, and make sure that you CONCATENATE all values. That’s why I added a comma after every activity in the Month field in the Main Table.

Something like this?


You could use ARRAYJOIN() as the aggregation function and skip the commas…

A
Forum|alt.badge.img+18
W_Vann_Hall wrote:

You could use ARRAYJOIN() as the aggregation function and skip the commas…


Thank you! I couldn’t find this solution.

A
Forum|alt.badge.img+18
W_Vann_Hall wrote:

You could use ARRAYJOIN() as the aggregation function and skip the commas…


@W_Vann_Hall There are some odd commas in the table when I use ARRAYJOIN()

Is there anything I can do about that?

E
Forum|alt.badge.img+17
Andre_Zijlstra wrote:

@W_Vann_Hall There are some odd commas in the table when I use ARRAYJOIN()

Is there anything I can do about that?


You have a separator parameter, use "": https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#text

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • 1386 replies
  • January 3, 2018
Andre_Zijlstra wrote:

@W_Vann_Hall There are some odd commas in the table when I use ARRAYJOIN()

Is there anything I can do about that?


Oh, sorry – to get rid of the extraneous commas when you have empty strings in some of your linked records, use an aggregation function of

ARRAYCOMPACT(values)

That would return Activity 2, Activity 1, Activity 5 and so on. If you need for the items to be concatenated without spaces between them — Activity 2,Activity 1,Activity 5 — you can use

ARRAYJOIN(ARRAYCOMPACT(values))

as your aggregation function.

A
J
2 people like this
  • A
    Alex_Whitton
  • J
    John_Beaudoin
A
Forum|alt.badge.img+18
W_Vann_Hall wrote:

Oh, sorry – to get rid of the extraneous commas when you have empty strings in some of your linked records, use an aggregation function of

ARRAYCOMPACT(values)

That would return Activity 2, Activity 1, Activity 5 and so on. If you need for the items to be concatenated without spaces between them — Activity 2,Activity 1,Activity 5 — you can use

ARRAYJOIN(ARRAYCOMPACT(values))

as your aggregation function.


That worked perfectly. Thanks!

R
Forum|alt.badge.img+3
  • R_Felciano_QIAG
  • Author
  • Known Participant
  • 13 replies
  • January 8, 2018
Andre_Zijlstra wrote:

The reply of @Andrew_Johnson1 is the fastest way of a summed result. If you want to achieve an Excel-like view it requires a lot more work.

I think of:
Creating a 2nd table (PIVOT TABLE) and have the primary field contain your type of events (unique names).
In your main table make sure you link your type of events field to the PIVOT TABLE.

In your main table create 12 formula fields with the names of every month. Have something like:
IF(Activity=BLANK(),BLANK(),IF(DATETIME_FORMAT(Date,‘MM’)=1,Activity&", “,”"))
This is for January (Month 1). Do the same for every other column. Of course you may hide all the columns when you’re done.

In the Pivot Table create 12 Roll up fields, and make sure that you CONCATENATE all values. That’s why I added a comma after every activity in the Month field in the Main Table.

Something like this?


Thanks @Andre_Zijlstra this looks pretty close to what I’m looking for. Any suggestions for how to avoid the stray / dangling comma at the end of the concatenated list of entries?

A
Forum|alt.badge.img+18
R_Felciano_QIAG wrote:

Thanks @Andre_Zijlstra this looks pretty close to what I’m looking for. Any suggestions for how to avoid the stray / dangling comma at the end of the concatenated list of entries?


Good to hear. You can get rid of the comma’s by useing ARRAYCOMPACT(values) instead of ARRAYJOIN(values). That should work fine.

Reply


Most helpful members this week

Terms & Conditions