Annual Average Chart

[Cameraville]

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.

[Darren_Murphy]

@Cameraville

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?

Yes.

@Cameraville

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 guess my question would be - why is that a problem?
It’s more or less what I would do, except that I would write the value as part of the navigation step that gets me to the details screen for each thing.
Unless you have a requirement to view charts for multiple things on the same screen - why isn’t this good enough?

[Cameraville]

For me it’s a problem because the selected Thing writes to the user profile column and therefore I cannot show these charts to non users. I have done this because I really don’t like the idea of making a user specific column on the Thing table, just to populate 299/300 items with a value that has no relation to that row.

Are my only two options to accept what I have now or to write the choice of the selected thing to all unrelated things?

Also this is an action on the table of each thing, so I cannot have the whole experience take place on a one-row non-user user-specific helper table. Or perhaps I could, but that also sounds very ugly and illogical…

[Darren_Murphy]

To make it work for non-signed in users, all you really need to do is move that user specific column to your Helper Table (the table that is the source of the chart).

I assume that table has one row per year, yes?

Given that, here is what I would suggest:

• Add a user specific column to that table to hold a ThingID
• In your Things table, create a Single Value → First → Whole row column that targets the Helper Table
• When navigating to the details screen of a Thing, use a custom action that first sets the ThingID in that user specific column via the Single Value column, and then does the Show Detail Screen as a second step
• In the Helper Table, add a Single Value column that applies that user specific column to all rows
• Use that combined with the year in a Query column to relate to the Sales data, then do your rollups through that query

[ThinhDinh]

Maybe in the helper table, you can use a JavaScript column that takes the current screen URL, and gives you the screen’s rowID. Saves some updates.

[Darren_Murphy]

Very clever - never considered that.