Reference Line Based on Time/Dynamic Reference Line

[wcawcawca]

Hi,

In one of our dashboards, the main visual is a stacked area line chart. It shows how many new users sign up, and of what type. It’s great.

A few weeks ago, we tried to think of ways to improve it. We came up with the idea of having it show our past monthly targets. We thought adding those would help show our past performance and how we’ve exceeded or missed monthly goals. Pretty important and seems fairly intuitive, we thought. Plus, I know I’ve seen this in other dashboarding platforms, so I figured it’s got to be doable here.

Something like this:

DISCLAIMER: I just whipped this drawing up in two minutes. The real deal need not be this smooth or anything.

However, I cannot figure out how to add anything remotely close to this.

My first thought was to overlay a reference line. However, with a reference line, either constant or calculated, I can only show a flat line with whatever single target I choose. Not really what we’re after. Again, we want a line or curve showing our historical targets, not a flat line showing a single target.

So then I thought about how our targets are calculated. We base these on a formula, whose sole input is date. I then whipped up a calculated field to produce the targets we want. To make sure I got as granular as possible as our visuals also can change their periods from days to years, I ended up doing daily targets. The formula looks like this:

ifelse(
datediff(parseDate(‘2022-12-31’), now()) > 0,
9357 * 1.00248195045^(datediff(parseDate(‘2022-12-31’), now())),
0
)

Yes, it’s a little wonky and is based on the end of last year and the current date. However, it produces the exact numerical output I want for any date after 12/31/2022, which is what I’m after.

This obviously didn’t work as a reference line. Therefore, I tried the hybrid bar chart and line chart. But no matter what I do, I can’t simply have the output of this calculated field display. The visual always takes the output of the calculated field and does other calculations to it–a sum, average, whatever. It’s trying to do too much! I simply want to display the output I’m directly calculating, and it doesn’t seem I can do that.

So…I’ve exhausted both options I know of and now I don’t know what to do. I don’t know how to simply plot a line based on a formula, and it’s driving me bonkers. Just a simple, simple curve with one input. I’m a math guy and this just baffles me. There must be a way, surely.

Ideally the reference line could be a line or curve and I could just give it my exact formula and this would plot. But again, the reference lines can only be flat. Why? Do most people simply want a flat reference line? I get that could work for visuals displaying mostly constant stuff over time. But for a growth chart? I don’t see how a flat line is viable there. A curve is necessary.

Again, I’ve seen other dashboards plot formulas, but I just cannot do the same here. Please tell me what I am missing.

I really am at a loss at this point.

[duncan]

Hello @wcawcawca and welcome to the Quicksight community!

After trying a few things out myself and looking through documentation about the way reference lines are described in the documentation I don’t believe this specific use case is possible. I would check out Line and marker styling on line charts in QuickSight and Reference lines on visuals types in QuickSight.

Have you tried creating a Stacked Bar Chart Combo for this use case or does it not represent the data correctly for your purposes?

[wcawcawca]

Hi, @duncan,

Thanks for the welcome!

And yes, when I referred to the ‘hybrid bar chart and line chart’ in my original post, I was speaking of the ‘Stacked Bar Chart Combo’. Unfortunately, as stated above, it just kept trying to do too much with my calculations.

And thank you for the links, but I had seen each of them a few times. I’ve gone high and low looking for solutions to this. But yes, thanks for checking!

[David_Wong]

Hi @wcawcawca,

ifelse(
datediff(parseDate(‘2022-12-31’), now()) > 0,
9357 * 1.00248195045^(datediff(parseDate(‘2022-12-31’), now())),
0
)

I’m not sure if I’m missing something but it’s unclear to me how this calculated field would give you anything that’s not a constant value. For this to be a curve, shouldn’t it be a function of the date on your x-axis? Everything in the calculated field is a constant, including now() because now() refers to the current date and time.

[wcawcawca]

Hi, @David_Wong,

Thanks for that tip. That makes sense and was an oversight.

However, when I change the now() in the datediff in the ‘then-expression’ to the same date field used for the x-axis in the visual, I still only get a flat reference line. I think this is because the always-on functions associated with the a reference line done by calculated field are only producing a constant?

[David_Wong]

Hi @wcawcawca,

I think the issue isn’t the actual aggregation because you could just set min, max or average and it would give give you the right result. The issue is that QuickSight does another calculation on top of it. For example, if you select “Average”, my understanding is that QuickSight takes the result of the aggregation and calculates the average across all your data points.

Instead of adding it as a reference line, what happens if you add it directly as a value in your visual and set the aggregation to avg?

[Max]

Hi @wcawcawca

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.