I have a table of 300 Things, they each have a multiple relation to a price table (which is around 1300 records of prices these things have sold at).
When viewing a Thing, I would like to see a chart showing the average selling price by year. Very simple request. Though I cannot find a viable way to do this.
By default a chart will display a sum of sales per year. This has no value to me. I need the average.
I’m trying to take small steps to determine the data format requirements to make this work - to help me craft some solution.
Ultimately I have determined that, the way the chart component works, I need to have each year as row, so I can select “year” for the X axis. Am I thus far correct?
If so, what is next? I fully understand rollups and lookups, but my difficult is in relating each Thing to itself in the Price table. If I have a column in the pricing table called ‘Things’ I can relate to that of course, but each cell will have all 300 things, because each thing sold each year.
Where I fail is, when the relationship is made, I can only pull in the average price from all sales of all things that year - I do not know how to get a rollup per thing per year.
I have crafted a temporary solution where the chart is behind a button, and the button click has an action which writes the current thing to a helper table and successfully makes the calculations and charts I’m looking for. But this means I can only calculate one things data at a time, and no ‘average price by year’ data exists for all 300 things.
I would love to get some ideas - hopefully I am doing this wrong and it is easier than I realise.
If my description is difficult to understand, essentially I would like a chart component where the values displayed are an average and not a sum.
Here is my desired result and current situation, but as I said the data is only now being calculated one Thing at a time which is not the desired backend.