Help

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

Topic Labels: Extensions
9458 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Majstor_Oglasav
4 - Data Explorer
4 - Data Explorer

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)

10 Replies 10

Hey Pierce, would you pls share me your approach as well? Or if it is more easier just hit me an email tt@akarni.com. It is really appreciated. Cheers. Have a great coming Christmas:)