https://evidence.dev logo
Join Slack
Powered by
I'm thinking that to do a year-over-year view, I w...
# troubleshooting
r
I'm thinking that to do a year-over-year view, I would likely combine two different charts, one with last year's data, one with this year's data, right?
b
What output are you looking for. Two lines? eg One is 2021, one is 2022, but against the same x axis?
r
yeah
ideally just the one query, but if I do a query per line
b
I would probably do something like:
Copy code
```my_query
select 
date_trunc("month", mydate) as date_mmmyyyy,
date_trunc("year" mydate) as year_yyyy,
sales
from table
<LineChart data={my_query} x=date_mmmyyyy y=sales series=year_yyyy />```
which will pivot out the data by year, creating a new series for each year
r
oh nice
that puts the lines next to each other
b
ah sorry yes there's kind of a bug in my code
r
yeah gotta do a groupby too 🙂
✅ 1
b
Also you need the first column to just return the month, not the year
Copy code
date_part("month", mydate) as date_mmmyyyy
date_part not date_trunc in this case
I can possibly imagine this will cause sorting issues but I may be wrong.
As it might try to sort the months alphabetically
r
brb meeting
b
me too!
okay around now till EOD
r
yeah same
I think I have it, but the x axis is just showing Jan
not the month, just jan
in both brave and firefox
b
could you screenshot the x-axis for me - feel free to crop off the data
r
Copy code
select 
    count (distinct aa.Id) as count_users,
    date_part(MONTH, aa.CreatedOn) as joined_mmm,
    date_part(YEAR, aa.CreatedOn) as joined_year 
...
where joined_year >= date_part(YEAR, dateadd(year, -1, current_date))
group by ---id,
    joined_mmm,
    joined_year
order by joined_mmm asc
b
So do you have your chart as?
Copy code
<LineChart
   x=joined_mmm    
   y=count_users
   series=joined_year
/>
r
yeah
Copy code
<LineChart
   x=joined_mmm
   y=count_users
   series=joined_year
/>
b
yeah sorry missed the data={query}
Hmm, and your data itself returned from the query You have one row per month, per year?
r
interesting
the inline says they're all jan
lemme check in snowflake directly
b
I dont suppose it makes a difference if you
Copy code
date_part("MONTH", aa.CreatedOn)
Does it?
with the quotes
r
yeah in sf it returns 1, 2, 3, 4, etc for the monthparth
doesn't seem to matter with the quotes
b
Copy code
select 
    count (distinct aa.Id) as count_users,
    date_part(MONTH, aa.CreatedOn) as joined,
    date_part(YEAR, aa.CreatedOn) as joined_year 
...
where joined_year >= date_part(YEAR, dateadd(year, -1, current_date))
group by ---id,
    joined_mmm,
    joined_year
order by joined_mmm asc
Copy code
<LineChart
   data={query}   
   x=joined
   y=count_users
   series=joined_year
/>
I see the issue now I think - the integer 1,2,3,4 is not a date type
Its an integer
so I think 1 represents 1-Jan-1970, 2 represents 2-Jan-1970 etc
r
ah
b
My suspicion is that the above code will work, but give you a chart with an axis running 1-12
r
yes
that it does, if I change the formatting
b
Okay so final option I think could do it:
Copy code
select 
    count (distinct aa.Id) as count_users,
    date_part(MONTH, aa.CreatedOn) as joined,
    MONTHNAME( aa.CreatedOn ) as joined_monthname
    date_part(YEAR, aa.CreatedOn) as joined_year 
...
where joined_year >= date_part(YEAR, dateadd(year, -1, current_date))
group by ---id,
    joined_mmm,
    joined_year
order by joined_mmm asc
Copy code
<LineChart
   data={query}   
   x=joined_monthname
   y=count_users
   series=joined_year
/>
use the monthname() function to get the month name as a string for the x axis But still sort it by the month number you are getting from the date_part() function
r
yeah I set sort to false to get that
dope!
now I have year over year!
🎉 2
Open in Slack
PreviousNext
Powered by