Add confidence interval capability to time series line charts

[atronchi]

Somehow I can't find any mechanism to display a confidence interval in any of the line chart types available in Superset, but it seems like the functionality is there under the hood since the new prophet based forecasting can display confidence intervals.

Would it be possible to add a capability to line charts for displaying a time series with confidence intervals similar to this Altair example?

Ideally we should support both symmetric and asymmetric confidence intervals, maybe by giving the user additional options in the query section of chart config to provide a column for upper and lower confidence interval bounds.

[rusackas]

This is a great idea. There might be some ways to hack a solution if there's something urgent (e.g a multiple time series chart, with area and line) but @villebro probably has the best context on generalizing a real solution (since he wrote the prophet feature).

[villebro]

@atronchi this functionality already exists on the "Time-series line chart", but is unfortunately undocumented. Let's say you have a chart with a series names. To add trend, upper and lower confidence intervals you can add the following series:

• names__yhat: trend/point estimate
• names__yhat_lower: lower confidence level
• names__yhat_upper: upper confidence level

You can achieve this by creating a separate metric for each (I mocked some series using the birth_names example data):

If I remove the names metric, I have a chart very similar to your example:

[atronchi]

Thanks for the tips @villebro! Some initial observations about using this previously undocumented approach:

• It will render multiple lines instead of one line and error band when the 'group by' field is populated.
• Even when I leave the 'group by' field empty, the error band appears to have 0% transparency. (screenshot below)
  For context, I'm using Superset 1.4.0 on Safari 15.1.
  

[atronchi]

Same behavior on the Chrome browser (version 99.0.4844.51) FWIW.

[atronchi]

It looks like most of the plumbing is there and it at least works fine in the Timeseries storybook demo.

[villebro]

@atronchi ohh, this is because of the opacity bug in 1.4.1 - this will be fixed by this: #18566 Let me kick off the vote for this rc so we can get 1.4.2 out asap.

[mattitoo]

Hi - I will comment here since I can't geti to work. Prophet is installed and works (creating forecasts).
But when I try to add __yhat as a metric, I get the following error:

Error: 000904 (42000): 01a4ca16-3201-2b60-0000-b01d011706ea: SQL compilation error: error line 3 at position 7
invalid identifier 'PAGE_VIEWS__YHAT'

This is the SQL that is cerated by Superset:

SELECT DATE_TRUNC('DAY', datetime) AS "datetime",
       count(*) AS "page_views",
       page_views__yhat AS "page_views__yhat"
FROM DATASET
WHERE datetime >= '2022-03-07 00:00:00.000000'
  AND datetime < '2022-06-07 00:00:00.000000'
GROUP BY DATE_TRUNC('DAY', datetime)
ORDER BY "page_views" DESC
LIMIT 10000;

I am using Superset 1.5.0 with "GENERIC_CHART_AXES" enabled.

[mattitoo]

@villebro Sorry to bther you, I might be a bit dense here - how did you create those metrics out of the columns (what is the SQL code for this)? I can't get it to work...

[JayaniH]

Hi @villebro

I tried to to create a time series chart for the birth_names dataset with a confidence interval using the data from the below query:
SELECT ds, num AS name, (num+1000) AS name_y_hat_upper, (num-1000) AS name_y_hat_lower FROM birth_names

For the Time-series Line chart I am getting three lines for the metrics:

How do I get the error band for this data?

[villebro]

@atronchi you need to rename metrics for the confidence levels to SUM(name)__yhat_upper and SUM(name)__yhat_lower if your main metric is SUM(name). Click on the title of the metric in the modal to do this.

[atronchi]

We've upgraded to superset 1.5.0, and still see no rendered confidence band. The opacity bug fix is shown as 'merged' now; did it get reverted in 1.5 or something? #18566 [image: image.png]

…

On Mon, Jun 20, 2022 at 10:23 PM Ville Brofeldt ***@***.***> wrote: @atronchi <https://github.com/atronchi> you need to rename metrics for the confidence levels to SUM(name)__yhat_upper and SUM(name)__yhat_lower if your main metric is SUM(name). Click on the title of the metric in the modal to do this. — Reply to this email directly, view it on GitHub <#19145 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABFNZ4DOSVBZALDPYWXGUGTVQFGW3ANCNFSM5QXCG3NA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[villebro]

@atronchi were you able to get this to work?

[atronchi]

I think we are still running 1.5 but I'll see about loading a 2.x release candidate to our staging cluster to test if it's working now. Thanks!

[jdwills]

Hi @villebro

Running Superset 2.0.0 and the confidence bands are working great in most scenarios! Mocked up a quick example using the birth_names dataset from your guidance above.

Unfortunately, when the __yhat* metrics pass through the zero y-axis they are truncated. As you can see below from the query, all I did was shift the values down by 1000.

Any help understanding if this is the expected behavior, a bug, or just something I am missing would be most appreciated.

[villebro]

@jdwills I've added support for this in ECharts 5.3.3 with this PR: apache/echarts#17086 There was already a bump PR to bring in the fix, but it caused a regression, and had to be reverted, bringing Superset back to ECharts 5.3.2. I'll try to open a new fix PR in the next week or so, after which it should be cherryable into 2.0.1.

[jdwills]

@villebro appreciate the quick reply and added context. Looking forward to 2.0.1.

[villebro]

FYI @jdwills I finally got around to opening a new fix for the negative confidence bands: #21916

[markostefanovic1]

Hey @villebro, I'm not sure how this works, I'm using Superset 4.0.1.6 on Preset and can't get the confidence interval to work properly if the metric goes below 0. I tried it in both line chart and generic chart:

This is how this chart should look like (Plotly):

[sonfire186]

How used SUM(name)__yhat_upper and SUM(name)__yhat_lower with DIMENSIONS

[villebro]

@sonfire186 One thing that needs to be mentioned is that you currently can't use grouping dimensions with confidence intervals (the series name needs to be end exactly in __yhat_*, and after grouping this is no longer the case due). It's perhaps something that should be considered, but right now it's unfortunately not supported.

[vlavorini]

Is there also a way to label the confidence interval?

I am using it in a mixed chart to compare data (in series b, scatter plot) with predictions (series a, lines)

[JhennerTigreros]

Hello @villebro, I have this problem trying to graphic confidence interval, we are using superset 1.5.0

[vlavorini]

it seems that at some point your lower bound is higher than the upper bound, or the data is sparse in the X axis

[JhennerTigreros]

I figure out that the X axis need to be a Date, not a raw integer.

[atronchi]

Does anyone else think it would make sense to promote this discussion to a new feature request for Superset? Given the additional discussion there are clearly several others interested in the feature and the current implementation is both undocumented except here and limited in capability (e.g. can't use grouping dimensions as @villebro points out above).

Even though I have kids and little evening time now for OSS contributions, I might even work on this myself since I often come back here hoping that it's done already so I apparently have a persistent need for such vis, but I would need more discussion with @villebro and maybe other superset devs to figure out what changes to make where to make it happen. Here's what I would like to see:

• extend the time series line chart to fully support confidence intervals including grouping dimensions
  • add UI elements under Data/Query section in chart explorer: checkbox (default unchecked) to enable confidence intervals, metrics selector for upper and lower bounds to support asymmetric intervals
• provide documentation and example charts of how to use the feature

Thoughts?

[nicouh]

Hi,

I am new to superset and have trouble showing the intervals. It says "Time-series line chart" is now legacy/deprecated, i.e., cancelled? I tried to follow the infos given here, using __yhat_upper etc, but it does not work and shows separate curves instead.

Any help is welcome, thanks!

[Redallium]

Hi,
Try renaming the "forecast" metric to "forecast__yhat"
Unfortunately, I still couldn't figure out how to add dimensions to such a plot, except for specifying n * 3 metrics with filtering. The plot immediately stops displaying the metric with a confidence interval and displays just 3 lines

[nicouh]

Hi,
thanks, that did indeed do something! I was so sure I already tried it.

Anyway, now the intervals and plotting seems to be wrong, see the three circles where the plots should be. The numbers are correct, but the drawn areas are wrong.
Is this a bug or still something wrong on my end?

Thanks

[Redallium]

Hi!
I also encountered this problem and it helped me to change the sorting parameters in the "Customize" tab to "Total Value"

[nicouh]

Hi,
thanks for your quick reply! That was already set, but I tried checking "SORT SERIES ASCENDING" and this seems to do the trick. Now it looks correct.

Thanks a lot for your help!
Best

[atronchi]

Still surprised that somehow error bars have not made it into the UI with more official support, but I guess I have to own that a little since I have also not sent a PR to fix the problem...

[nik-baj]

(SOLVED)

Hello @villebro , Hello everyone,
Having gone through the entire discussion here, I am trying to put confidence intervals. I tried two approaches (both mentioned in this discussion) but in either case, I get these lines itself and not the shaded area between these lines.

Superset Version: 4.1.1
Chart used: Time-series Line Chart (legacy) - Deprecated (happy to change this if I am not using the correct chart)

1. The main metric names as SUM(total_price) and the lower and upper bounds named as SUM(total_price)__yhat_lower and SUM(total_price)__yhat_upper, respectively.
   

2. The main metric names as SUM(total_price)__yhat (added yhat to the main meteric) and the lower and upper bounds named as SUM(total_price)__yhat_lower and SUM(total_price)__yhat_upper, respectively.
   

Then I tried a third approach wherein I used the Line Chart with the first approach that @villebro suggested. While it helped with the interval issue, it introduced a tiny bit of another one that now the line chart isn't a line chart anymore; it is a scatter plot. The "Series Style" in the "Customize" tab is set to Line

Solution

Following on the solution mentioned here, I was finally able to get this right by renaming the main metrix to SUM(total_price)__yhat in the new Line Chart (not the deprecated time series line chart)

[nik-baj]

Hello again, everyone,
Since I am new to Superset and testing it, I am finding some additional issue and would like to report it.

While showing the confidence interval now works by using the renaming functionality, the data labelling does not work.

But if I were to rename it ahain (remove the __yhat from the main metric), then the data labelling comes back, but the lines is gone now and it becomes a scatter plot again.

Lastly, the data zoom functionality does not work in either case. If you were to look at the bottom of the chart, only the forecasted value show some peaks. Everything else is flat despite data being present.

Do you know if I need to report it as a new issue or posting it here is enough?

[nicouh]

Hi,

thanks for the previous help. I am still stuck a bit, since some plots work, and some do not, like 50-50. I can switch which half works and which does not by checking "SORT SERIES ASCENDING" (no idea what is being sorted, btw), but I have not manged to have all plots work at the same time.

I have one Dataset from which I extract a table and a plot which plots the data filtered by what is active in the table.

One plot works:

The other does not:

Switching between having the intervals be correct or not:

and

What do I have to change such that the intervals are correct for all plots, not just either half of them?

Thanks and best