https://evidence.dev logo
Join Slack
Powered by
how do I enforce an order in the x axis by month/y...
# troubleshooting
r
how do I enforce an order in the x axis by month/year for a stacked bar chart? The chart seems to want to order the bars by the size of each bar, not the actual date/time
👀 1
b
You have a couple of options here 1. If the date column is stored as a date type in your DB (as opposed to a string etc), then Evidence should order it chronologically. a. This is not possible eg for SQLite which does not have native date types 2. Otherwise, order it how you want in the SQL, and add the sort=false prop to your chart
eg
Copy code
<BarChart
   data={myquery}
   sort=false
/>
I do wonder if our auto-sort behaviour is confusing. Perhaps it should be off by default?
✅ 1
r
maybe?
well, can I order by month and year?
the trouble is I need to do a stacked bar chart of signups by month. I can group by the month, but then the ordering is off (dec appears before may of 2022)
so I can have a column that is the actual year/month, and another that is the "pretty print" year month
but that seems odd too, nomsain?
(also, how do I label my axes? so that the labels appear in the graph?
b
(also, how do I label my axes? so that the labels appear in the graph?
Use the props: 
xAxisTitle
, 
yAxisTitle
l
@bright-smartphone-11251 i had same issue, was unituitive to figure out sorting but once i get it figured out, it worked great
b
the trouble is I need to do a stacked bar chart of signups by month. I can group by the month, but then the ordering is off (dec appears before may of 2022)
I don't fully follow - could you give example code / screenshot
l
data type is string, so its alpha sorting the dates i think.
✅ 1
b
What DB are you using?
r
Snowflake
I can't share screens without an NDA sadly
b
All good
So probably the best solution would be to cast the column to one of snowflake's native date types
l
r
so I can get it into months/years etc
b
And then for formatting the dates as you need, use Evidence's format tags
r
so I have three types, and I want to stack those three, and group by month
b
I like using snowflake's date_trunc() function for this
r
so datetrunc and then throw it into the group by?
b
yeah
r
cool lemme check it out
b
and if you need to change the format of the date that is printed, you should be able to see the different tags (or create your own custom one) in the settings menu
r
so I can do "mmm yyyy"?
✅ 1
is that something like "yAxisFormat=mmm yyyy"?
l
you have to define it and alias it in the settings pane
like a custom format in excel
r
and then reference it in the chart how?
b
You append the tag to the end of the SQL variable name in your query, using an underscore
Copy code
mydate as mydate_dmy
for example.
but in your case, use the custom format you have defined
As a secondary point, I think 
mmm yyyy
is probably a common enough date format that we should add it to our built ins
👍 1
r
when I look at saved formats, it says sample "Jan 3, 2022" and the result is Jan 1900
with format code mmm yyyy
b
Hmmm, that is an interesting bug
How does it work with your actual data?
r
it does the whole datetime string
2019-07-01 000000.000 + 0000
do I maybe need to restart the server with the new format?
b
possibly, I'm not sure.
If that doesn't work, could you share your SQL code that you use to define that variable? Just that one line is fine
r
rebooting didn't help
Copy code
date_trunc(MONTH, aa.CreatedOn) as joined_monthyear,
b
Final question: what browser are you previewing on?
r
firefox
on brave, it just shows the year
no month
tooltip shows mmm yyyy
b
Is that on the chart axis that it's just showing the year, or in the query result?
(in brave)
r
chart axis
b
So the X axis label works a little differently: If there is not loads of space per bar, then it will not show a label for each bar, as it gets congested, and instead show a label every x bars My suspicion here is that the Brave version is working as we would expect, but Firefox has a bug with parsing snowflake dates.
r
can I turn the x axis labels to be vertical?
maybe I shouldn't do that
b
How many month columns do you have on your x axis? Just 12 (ie one year)
r
last seven years
s
This is really confusing for me to, I have a duckdb query, I format my x axis as text (YYYY-MM) I see the values nicely in the query editor, but if I put it into a datatable or barchart (with sort=false) I get dates in the datatable (evidence trying to magically detect dates from strings perhaps?) and the bars doesn't even show up in the bar chart. My data stems from bigquery but is grouped and fixed inline:
Copy code
```sql cost_per_month
SELECT
  year_month,
  SUM(cost) AS month_cost,
  month_cost - lag(month_cost) OVER (ORDER BY month_cost) difference_to_previous
FROM
(
  SELECT
    strftime(cost_date, '%Y-%m') as year_month,  
    bd.*
  FROM billing_data bd
)
GROUP BY year_month
ORDER BY year_month
<DataTable data={cost_per_month} /> <BarChart data={cost_per_month} x=year_month y=month_cost yFmt='usd0' title='Monthly Cost' yAxisTitle='Cost', xAxisTitle='Month' sort=false />```
b
Evidence does try to detect dates from strings in some components. This is somewhat of a hangover from pre-USQL. Some databases (eg SQLite) do not have native date types, so it was necessary to infer dates from strings to make it possible to plot time based axes in these situation. In the USQL DuckDB, this is no longer strictly necessary, so we will probably look to remove support for this In the meantime, is there any reason you cannot use 
date_trunc('month', cost_date)
?
If you need your date to appear as YYYY-MM you can use that in a 
fmt
string
Copy code
<DataTable data={sales}>
  <Column id=month fmt="YYYY-MM"/>
  <Column id=sales fmt='"$"#,##0'/>
</DataTable>
CleanShot 2024-02-01 at 09.11.35@2x.png
s
Doesn´t the ordering get wrong if you use date_trunc when you span over a year?
I would want 2023-10, 2023-11, 2023-12, 2024-01, 2024-02
b
I doubt it
Seems fine to me
date trunc basically does 
2024-01-27
-> 
2024-01-01
- it's still a real date at the end
s
date_trunc worked fine, thank you
👍🏼 1
b
and then we are just formatting the date so we are just displaying the year and month
👍 1
4 Views
Open in Slack
PreviousNext
Powered by