Vega-Lite Histogram

[CharlieB]

Hi,

I'm not sure if this is the right place for this, but here goes. I have a table containing the following 'data'

Project, Person, Start, Finish

I want to plot on a Vega-Lite Heatmap style Histogram the 'heat' of these people over time. So the more 'Projects' then the higher the 'heat'. I could see that Joe is cold in Jan, but hot in July.

This is the style of heat map: https://vega.github.io/vega-lite/examples/rect_binned_heatmap.html

I guess I need to transform the 'hits' per say, month. But I have no idea how to do that!

Thanks in advance.

[Jan_Cape · Accepted solution]

Hi CharlieB,

I used a formula generated by Kuovonne https://community.airtable.com/t5/formulas/return-months-from-start-end-dates/td-p/35722 and extended it to cover projects up to eight months in length.  This generates a list of all the months in the date range.  Then I created a month table and an automation that links all the projects that are ongoing in that particular month (it's important that each date in the month table matches the MMM YY format from the Projects table > Months field exactly).  Finally, I created a field to count each person's projects in a given month.  I think you'll be able to use this data to create the heat map.  Here's the test project: https://airtable.com/shrGZkVwoGwyQYhqh

Hope this helps! 

[CharlieB]

Thanks! I haven't quite got it working yet but I think I understand. Appreciate your time.

[CharlieB]

Actually I haven't got it fully understood. I have all this information in 1 table. Projects being the primary field, everything else is just columns of data. When I create a new table with the months going down, when I create a 'Projects' column, Link to another record - I don't see 'Projects' but rather my 'main' table and my new one. I select the main table and then confirm, it asks if I want to create a 'lookup' field, so I'm like, sure. Select the 'Projects' field but it just makes another column, and both are completely empty?

It looks like your test project, but I must be missing something....

Thanks.

[Jan_Cape]

It sounds like you have everything set up and just need to create the automation that will populate that currently empty linked field.  The example is set to trigger whenever you change something in the Projects (Main) table.  Let me know if you have any trouble setting it up or getting it to work.

[CharlieB]

I don't see anything in the 'Automations' area, in the example base? Do they not carry across (when I saved the example to my account)?

[Jan_Cape]

That's strange - I'd expect them to duplicate along with the base.  Attaching screen grabs of the automation setup.

Step 1

Step 2

Step 3

Step 3 detail

Step 4

Step 4 detail

[Julie_D]

 

Hi I'm following along exactly with your instructions but I'm having an issue with the 'Step 3 detail' when selecting the 'Dynamic' option. with the condition "Where 'Months' contains 'Month'" I am not able to select the 'Month' table but instead it says "Cannot assign list of String to String"