How to create a "fake" pivot table, with recurring records (over months)?

[Majstor_Oglasav]

Hi all,

my first post here :slightly_smiling_face: after a couple of weeks of using this AMAZING product. I am working on a small private project, where I want to track my expenses and income via forms on my mobile. The final product should be something like a monthly pivot chart which would show my expenses and my income streams over months.

How I used to do this in Excel? I entered as many unique records as there were income/month/year combinations and do a simple pivot chart.

Example of a pivot table I’d like to achieve:

 --------------+-----+-------+-------+------+
               | jun |  jul  |  aug  |  sep | (oct...)
 --------------+-----+-------+-------+------+
revenue        |     |       |       |      |
  -salary      | 2000|   2000|   2000|  2000|
  -rent        |  100|    100|    100|   100|
costs          |     |       |       |      |
  -utilities   | -200|   -200|   -200|  -200|
  -car payments|  -70|    -70|    -70|   -70|
  -vacation    |     |   -500|   -500|      |
TOTAL:         | 1830|   1330|   1330|  1830|

Example of raw data:

- salary jun 2017 1000
- salary jul 2017 1000
- salary aug 2017 1000
- salary sep 2017 1000
- rent jun 2017 -100
- rent aug 2017 -100
- ...

I tried doing this by moving everything into separate micro tables:

• Table 1: months
• Table 2: years
• Table 3: CostRevenues

Records from CostRevenues link to multiple records from tables months and years since these records repeat… but I am not getting what I want.

Will I have to create 12 salary records for 12 months in a year?

Thanks!
Alex

Link to Airtable grid view of a problem(GoogleDrive hosted image)

[Pierce_Baugh]

One solution is to create a number of calculated fields in your raw data table.
Step 1.
The first is a simple concatenation of month and year. So MonthYear={month}&"-"&{year}
Step 2.
Then you have to set up if statements in columns
IF(MonthYear=“jun-2017”,Amount,0)
Step 3.
Now you can group that data by your primary column (salary/rent/etc) If you want add a revenue/cost category you could just add that column to your raw data.

[Majstor_Oglasav]

Pierce, thank you sincerely for taking the time to help me out, much appreciated! :winking_face:

However, I don’t think that I fully understand how I’m supposed to do this. If you’ve got a couple of minutes it would be spectacular and I would definitely learn something new.

Here is a link to the base, maybe that will make it easier for you to understand what I am doing wrong:
Example base, wish to create “pivot” report

I don’t have rawData table anymore, that is how I did it the old way, in a spreadsheet. I wish to utilise db features and spread everything over separate tables, which are then joined.

Many thanks for your patience!
Alex

[Pierce_Baugh]

Alex
I’ll share a sample with you, what’s your email.
Pierce

[Majstor_Oglasav]

you can send it to majstoroglasavanja@gmail.com

Cheers!
Alex

[David_Isaac]

Hi Pierce- I’m trying to do something similar. Perhaps you could share the details with me as well?

[Pierce_Baugh]

Hi David,
Yes the principle is simple, you use if statements to “flatten” out the data, so let’s say we want to put revenue into month columns but our input is by date.

We would create function (calculated) columns 1-12 for each month, and put the following function in.
January would look like this: IF(MONTH(TransactionDate)=1,Sale,0)
Where TransactionDate is a column with the date of the transaction and Sale is a column with the sale amount. The new January column will only show revenue from January. You can then do views that group by any specific column you want.
BTW February would look like IF(MONTH(TransactionDate)=2,Sale,0)

[Katherine_Duh]

Something that might be relevant to your interests: as part of our newly released Airtable Blocks platform, you can now create “real” pivot tables! :tada:

Airtable - Support

Pivot table block

The pivot table block is part of Airtable Blocks, a Pro plan feature. Blocks let you extend the functionality of your bases: you can use blocks to bring new information into Airtable, visualize and...

[arc]

Hi Katherine, Pivot table block looks great, but I don’t see a way to link to that in the main tables. Is this possible?

[Jeremy_Oglesby]

In the settings for a Pivot Table block you are required to enter the Table and View you want to pull the data from. Then you tell it which Fields from that Table to use as columns and rows in the pivot chart.

If you need to compare data from two different Tables, what you’d need to do is create a third Table that links to both of the Tables you want to compare data from (this is called a “Join Table”), and use lookup/rollup fields to get the data you need into the third Table. Then you can tell your Pivot Block to reference the Join Table.