Create a heatmap

[napoleon86]

I would like to create a heat map with data from a related table.

In table 1 different records with a field with a value. A field with hour of the day. A field with day of the week.

In another table I would like (in 1 global record) an overview with times per day with a field containing the average value of that hour of the day of the week.

What is the best way to do this? Thanks

[comment]

I am not sure why you would need the other table. You can simply produce a summary report of your data, sorted by day-of-week and by hour, and use a summary field to display the average value of each sorted group.

 

Edited August 5, 2019 by comment

[bcooney]

xpost https://community.filemaker.com/en/s/question/0D50H000072ok3SSAQ/create-a-heatmap

[napoleon86]

I've uploaded a file. In Table "Content" i have the data. In Table "Heatmap" I want the average of the field "Value" of the related records. 

 

Thanks!

Naamloos.fmp12

[comment]

As I said, I don't think this is a good approach. To populate just 4 hours x 5 days of week, you would need 20 calculation fields and 20 relationships (or 20 filtered portals).

[napoleon86]

and how will these calculation and relationships look like?

[comment]

Are you seriously contemplating this? I only mentioned it to show how impractical it would be. There are much better ways - but you need to explain in more detail what exactly are you trying to accomplish here (and for what purpose).

Edited August 8, 2019 by comment

[napoleon86]

☺️☺️

I have an existing database (with around 10.000 records and growing) with a timestamp and a value. From this database i want to make a dynamic heatmap with the average value per our of the day.

[comment]

I am afraid that doesn't tell me anything new. I don't know what you mean by "a dynamic heatmap". As I said in my first response, average values are easily produced using a summary report - see the attached demo.

DailyAverages.fmp12

[napoleon86]

Yes, this looks like it what i'm searching for. Many thanks!!

[napoleon86]

another question to make it even more difficult 😂

Is there a possibility to change the background colour of a field when the average value is 0-50: red, 50-75: orange and 75+ : green?

or maybe above total average green and below total average red?

[comment]

Yes, this is easy to do with conditional formatting; 
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fconditional-formatting.html

Note that if you want to compare an individual value to the average, you must decide what to include in the average. If you use the existing summary field, it will be the average of the current found set.