Trend Visualization (Aka Smart Number) Custom SQL Query

Hey Folks,

So, I’m trying to build a trend visualization with a custom SQL query. One of these:

The docs state that:

To use this visualization, you’ll need to have a single number grouped by a Time field, like the Count of Orders by Created At.

I’m almost positive I have it set up this way, but am not able to get it to generate a comparison. All I get is the sad message “Nothing to compare for the previous .”

Here’s an example of the query. Currently grouping by date_trunc but have also tried to_char and various other postgres date functions. I have also tried limiting the results by 2.

Any help or guidance would be appricated!

Currently running v0.33.0

Hi @pjatx
Interesting. I was trying to reproduce your issue and forgot to order by date descending (meaning it was ASCending) and that worked, but when I sorted by date DESCending, then I’m getting Nothing to compare for the previous .

@sbelak Is that intended behavior? If so, then we should update the docs.

1 Like

Thanks @flamber!

That was indeed my issue as well. Looks like you don’t need to limit the query to 2 rows as well was my initial reason for ordering it by date desc.

It’s not really intended, but rather a side-effect of how we check if you have evenly spaced data (ie. if it makes sense to do a comparison). Mind opening an issue for this.

For reference: