# Re: Vega-Lite Histogram

Solved
1280 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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!

1 Solution

Accepted Solutions
5 - Automation Enthusiast

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!

7 Replies 7
5 - Automation Enthusiast

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!

4 - Data Explorer

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

4 - Data Explorer

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.

5 - Automation Enthusiast

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.

4 - Data Explorer

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)?

5 - Automation Enthusiast

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

4 - Data Explorer

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"