[Lens] Compare against time offsets

[wylieconlon]

As part of my work on the UI for defining calculations, I have considered the role of time offsets such as month over month as it applies to our visualizations (including visualizations that don't support layers). Specifically, I propose that the Lens editor can add additional columns with a time offset to any visualization that supports time. To give a simple example, let's consider the Top 3 categories this week query, which returns:

Category	Sum of price
Women's Clothing	1,230,602
Women's Shoes	622,456
Women's Accessories	367,128

Now I want to compare these numbers to the previous week, so I click the "Add time offset" button, and say "1 month". The final table is now:

Category	Sum of price	Sum of price (offset)
Women's Clothing	1,230,602	992,302
Women's Shoes	622,456	509,550
Women's Accessories	367,128
Men's Clothing		423,116

What the Lens editor has done is create a new datasource layer with a different context, and executed what is commonly called a FULL OUTER JOIN in SQL terminology. The visualization doesn't need to know where the data came from, it just needs to be provided with a set of column definitions.

Doing time shifting on date histograms

One of the common use cases for this is to use time shifting to compare something like "today at 12pm" to "one week ago at 12pm". The FULL OUTER JOIN wouldn't match the dates in the date histogram. The simplest approach is to use a library like moment to add the time interval to each date field in the results. It's about as good as we can do, but there might still be some degenerate cases like leap days or daylight saving time. A good example is:

Date	Category	Sum of price	Sum of price (offset 1 month)
3/28	Women's Clothing	1,030,954	992,302
3/29	Women's Clothing	1,206,230	924,743
3/30	Women's Clothing	1,230,602

Which time offsets should we support?

I would propose that we keep this to a simple list of mostly easy-to-divide intervals

• 1 hour
• 1 day
• 1 week
• 4 weeks
• 1 month
• 1 year

Dependencies

Esaggs: #71287

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[wylieconlon]

There are at least four ways that I can imagine this data being visualized:

1. Directly comparing the two values, such as in two line series
2. Using only the percentage difference, such as in a metric that says "30% increase year/year"
3. Showing the current value and percentage difference, such as "5.2Tb, 30% increase year/year"
4. Showing both the raw values and percentage difference, for example by overlaying bars for percentage over lines showing the actual values

This implies that every time the user wants to add a time offset, we will add three calculated columns which are supported by the Lens editor. These columns will need to act like references which will behave differently than our current query-building model. This requires some product and design input @AlonaNadler @cchaos

The user flow I'm imagining is basically the same in all the visualizations we support. There are two main examples:

XY visualization:

1. The user configures any query, not just a time series query
2. When they choose their time offset from a menu, we might default to adding the older value as a second Y axis
3. The user actually wants to see the percent comparison only, so they click into one of the Y axis dimension and select the reference to the precomputed column
4. The user deletes the second Y axis that wasn't needed

Metric visualization:

1. The user configures a Sum(sales price)
2. The user configures a time offset
3. The metric visualization is updated to show the current value and the older value, like in
   [Lens] Metric visualization with time based comparison #66222
4. To change the mode, a new set of options appears in the metric configuration where the user selects from the difference references

[AlonaNadler]

I see the comparison feature in the following way. I think we need a dedicated option for comparison that can apply to any calculation (average, sum, complex future calculation etc..)
When choosing compare users can fill the time they want to compare to :

• 1 hour
• 1 day
• 1 week
• 1 month
• 1 year
• custom

once choosing the interval allow them to show the comparison calculation based on the same function (average, sum, complex future calculation etc..) or as a percentage change (might be other options here)

[wylieconlon]

@AlonaNadler that sounds very much like what I proposed above. It seems like you were wondering about how this would work with any existing features, which I was trying to show above. To say it again in a different way: the way time offsets should work is on the entire layer/query. Internally, we are going to create a copy of the query with with different filters. This approach will work with any query.

[AlonaNadler]

Sounds good @wylieconlon.
I could think on two options to do that:
First:

• users configure sum(bytes)
• bellow in the same popup there is another option might be a checkbox for compare then when checked, make more options visible
• user choose the interval from a dropdown
• by default it does a comparison to sum(bytes) from previous {{interval}}
• option to change the comparison to be %based.
  The popup might be a bit bust in this case and we will need to add a comparison checkbox in every calculation.

Second option:

• users configure sum(bytes)
• user can clone the metric to appear as another metric (another y-axis, another column etc..)
• we have a generic function which is called comparison that takes existing calculation and allow users to configure the time comparison intervals and type of comparison

This approach also allows to delete the original calculation, need to figure how to change the calculation (I want to change it to sum(CPU) or the average of bytes)

[wylieconlon]

Sounds like there are multiple possible ways of presenting these forms to the user. To focus on the user experience, we should consider how the user would create all 4 visualizations that I wrote above:

1. Directly comparing the two values, such as in two line series
2. Using only the percentage difference, such as in a metric that says "30% increase year/year"
3. Showing the current value and percentage difference, such as "5.2Tb, 30% increase year/year"
4. Showing both the raw values and percentage difference, for example by overlaying bars for percentage over lines showing the actual values

As long as the user can do all 4 of these, I think we'll have a good design.

[VijayDoshi]

If these are calculated within the visualization; how will they interact with alerting? For example, alert me when day/day exceeds a certain value.

[wylieconlon]

@VijayDoshi We don't support alerting from any of the visualizations in Kibana, so I don't see how that question is related to this.

[VijayDoshi]

@wylieconlon We will eventually support alerting, right? Are we building something we'll just have to revisit once we decide to add alerting, can we prevent that?

[wylieconlon]

@VijayDoshi Alerting is not in our Lens GA roadmap, and in fact hasn't been discussed in the context of Lens at all. My intuition is that there is no conflict because alerting will use expressions, and so does Lens. If you want to discuss it in more detail, please open an issue for the topic of alerting in Lens.

[VijayDoshi]

@wylieconlon added #71150 to evaluate Alerting from Lens Visualizations.

[wylieconlon]

@cchaos @flash1293 I've been thinking about how this feature should work, and have made two videos to show you my thoughts. As a reminder of the goal of the time offsets, users should be able to create charts like this which shows current, previous, and percent difference for time offsets:

I would be comfortable going in either of the two directions shown by these videos:

1. Per-dimension settings:

2. Per-layer settings:

[flash1293]

I think I like the dimension setting because it allows you to do everything within in a single place without switching context and is a better fit for using time-shifted values in math operations (which is a common use case I think)

[wylieconlon]

The esaggs query setting has been put on the @elastic/kibana-app-arch roadmap. I think there is not a lot of ambiguity left in terms of the implementation of this feature, except for the dependency on the esaggs implementation.

[bradquarry]

+1 time series visual builder has this capability and it's used extensively within our customer base.

[wylieconlon]

It's important to note that this feature would be more powerful than what TSVB offers because we would also support calculations across time periods- in TSVB you can only view data with different offsets, but you can't subtract the two series or divide the series.

[ghudgins]

in addition to Previous can we also consider another dynamic range for Year over Year (same period a year ago)? This addresses seasonality

edit: okay you just type 1y 🤦