I want to display a 3 year run rate YoY on a single visual. I’d like to have the run rate for each year displayed on the same x-axis (full calendar year) in a line chart with 3 lines, each representing a separate year. My data is daily and includes a date field (date).
I’ve created a calculated field for the run_rate, calculated daily over each year:
This will be my value, the x-axis I’d like to have day_of_year (ie 1-365) which is a data point in my table. The year number will be a color, so I can see all 3 years stacked on one another at the same day each year to start looking at trends.
When I try to build this visual, I get an error that date isn’t included in the field well since that is in my calculated field for YTD_Sales, but the line chart is using a different value for the x-axis (day_of_year). I can’t find a way to calculate the run rate on the dayofyear as that is a string and not recognized as a date field.
Any suggestions on how I can achieve this visual without altering my source data downstream?
Hello @Kmllr15, how are you calculating the Day of Year for your X-axis? I believe if you utilized extract('DD', {Date}) as your x-axis, extract('YYYY', {Date}) as your color/group by field, and then your sales field as your y-axis, you may be able to make this work without the calculated field. Let me know if this helps!
Hi @DylanM thanks for reaching out. The dayofyear and year are data points in my table from the source. They are calc’d just as you have it in our date dimension and pulled through into the view in QuickSight. The calculated field I have is to sum daily YTD values for each day in my data set. Ie. June 24th 2023 has a net sale amount for that single day, and the calculated field gives it the YTD value on June 24th (Jan1-June24th). I need that calculation to be pre-filter, pre-aggregation but I can’t find a LAC for this use case.
Hello @Kmllr15, I have not been able to figure out a way to do this with LAC-W aggregations so you could use PRE_AGG or PRE_FILTER. Are you wanting to be able to control that rows that are being summed by a filter or are you wanting to ignore the filter? Maybe we can find a work-around.
I will have a filter on the visual to restrict some product sets from displaying, but nothing involving a date filter. I’m going to have my engineer add this as a column to the source view, but would be interested if there is a work around as this is a visual I’d like to use with other datasets.
Hi @Kmllr15,
It’s been awhile since we last heard from you. Checking in to see if you still had questions regarding your initial topic or if you were able to find a work around for your solution?
If we do not hear back from you within the next 3 business days, I’ll go ahead and archive this topic. However, if you have any remaining questions past that, feel free to create a new topic in the community and link this discussion for relevant context.