Show last (current) value of a trend line

(Dawid) #1

Is it possible to show the last/current value of a trend line? I’m using moving average calculations and would like to know its value as of the last data point on the graph?


(Izzy) #2

I can’t think of a way to just show the last value, but since the Moving Average trend line is effectively just a table calculation with the function mean(offset_list(${field_being_averaged},0,7)), you could create a table calc and plot it with value labels turned on, which’d show the last value (as well as all the other values).

Actually, you could even Hide No’s from visualization to only show one value for it!! I think that would work swimmingly. You’d need 2 calculations, though.

If you’re curious about moving averages in general, @quinn.morrison wrote a wicked article with some more deets on building this with a table calc.


(Dawid) #3

Hmm I will try your approach. I wish the Show Label switch would allow us to choose what should be the label (last value, text, name).

If I understand correctly without showing all data values I won’t see its value even if I hover over it, right?


(Izzy) #4

Yeah, that would be neat! There’s a way to do this with html that you should check out

but like Zach says, “It could be considered hacky by some”. I agree a slick way to do that with a toggle would be really nice!


(Brecht Vermeire) #5

It’s relatively simple to create a trend line, and a value label only for the last part of the trend line, here’s an example:

Like Izzy also mentioned, calculate a rolling average (be aware of the sorting of your data since it affects the offset). This calculates the average of the last 7 days including today, assuming the data is sorted ascending:

mean(offset_list(${orders.count}, -6, 7))

Create another calculation to show only the last value:

if(row() = max(row()), ${rolling_avg_7d}, null)

Now play around with the viz options a bit. Assuming that I have a regular measure and two table calculations (rolling average and last value) and the columns are in that order, enable value labels and add transparent, transparent, black as colors. This hides the first and second series labels and shows the third (the last value) in black. Play around with showing/hiding nulls and such until you got a pretty chart :-).


(Dawid) #6

Thanks @brecht It’s a nice way of achieving it. I tried to hide nulls but couldn’t find a way to do it but also after these two calculations my look became so slow I had to wait 20s to do one action. I know this is probably my machine’s memory problem but relatively it was fine with one table calculation but after having added two more it became a nightmare…