Foundry-workshop : add average value on a chart displaying parameter evolution

Question

I'm looking for a way to display a line showing the average value of a parameter in a chart dedicated to this parameter's evolution.

I have a dataset, let's take as an example the following structure :

Product |  Month  | Price
P1        2021-01   13.00
P1        2021-02   13.50
P1        2021-03   15.00
P1        2021-04   14.50
P2        2021-01   3.00
P2        2021-02   3.50
P2        2021-03   5.00
P2        2021-04   4.50

In a chart, I display de price's evolution for each selected product (multi select filter upstream) and I would like to add, also for each product, a line showing the average for the displayed period.

I tried so far 2 different approaches:

• Use multiple series and add one dedicated to this average. But I did not manage to calculate this average. Actually, to display the initial chart, the evolution of my property, it looks like I must use an aggregation function (each layer type requires to define series where the first parameter to define is an aggregation function)
• Create a summary dataset, with aggregates values for each product, and the latest calculation date. It looks like this:

Product |  Latest Month  | Avg Price | Max Price | Min Price
P1           2021-04        14.00        15.00       13.00
P2           2021-04         4.0          5.00        3.00

But I'm not able to overlay these values, as there is no time series to define the same X-axis.

I considered a 3rd solution, but looks dirty to me: to add the aggregate values in the first dataset. Each row would contain avg/max/min values for the period of time so that I can display these values the same way as any other property.

Finally, writing this post made me wonder if I well understood how this tool works, as I feel that what I implemented should have led to display the average values I'm looking for, but it's the only way I found to display a "simple" property's evolution.

Thanks in advance for your help.

Answer 1

As far as I understand, according to @Logan's answer, the documentation, my tests, the solution is based on a key feature I did not really understood before: a chart always displays an aggregate value of a property.
It's something I noticed and mentioned in my question, and I just understood how this aggregation is performed: there is no parameter to define an interval of any kind, but when your X-axis is a date, you need to select a bucket, and that actually is the aggregation interval. If your date is daily basis and you decide to display values at weekly level, the aggregation is based on 7 days.

Thus, the solution I found was just to add exactly the same layer than my monthly values, I just changed the bucket in the second one to display yearly values (it was actually what I was looking for, my sample data were just one quarter because I did not see it would have had an impact).

But if I needed to visualize a quarterly average (@Logan's answer made me asked), how should I proceed?
I assume I would have several approaches: the ones described by @Logan, even if I'm still doubtful about the first one. At least, I looks like function-backed chart would work, but I do not know typescript at all to implement such functions. Otherwise, preparing data in the same dataset, or in an another one, designed to be displayed with the same scale, might work as well.

How to deal with a rolling average? Well, I'm not sure it's possible, the only solution I see is function-backed chart but, one more time, I do not know typescript... I would probably use Slate instead, where I'm sure I'll be able to implement it.

Of course, any comment is welcome here, as I am still in a discovering phase of this tool.