I’m trying to show a visual for current date data and right below it (in a separate visual - line chart) have a 60 day trend (last N days) of the data. I want it to align which means I need to filter a trend to only users that also have data for today. Right now if a user leaves the dataset they will show up in the trends for 60 days while disappearing from the most recent day visual. Is there a way to filter a line chart so that it shows a trend of only users that have current data for today?
Welcome to QuickSight Community. thank you for posting your question.
if you could create calculated field to check if there is trend data for today like the below and then apply filter based on the calculated field, I think your line chart should be filtered with trend having today.
ifelse({trend}>0 and dateDiff({date},now())=0, {trend}, null)
let me know if this works or not.
kind regards,
Wakana
hi @Wakana -
Thank you for the reply. I did think of this same filtering method. It should work, but I get a mismatched aggregation error when creating the custom field.
“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”
The field is essentially what you wrote here:
ifelse( {count} > 0 AND dateDiff({date},${Date})=0,{count}, NULL )
Not sure why it would throw this error.
1 Like
hi @Wakana - It is a count yes. Even if I try to make it just a filter to utilize on the visual, I run into the issue that it restricts to only that one date, instead of a time series trend.
would you be able to replace {count} with calc filed using LAC with countOver() countOver - Amazon QuickSight using PRE_AGG ?
kind regards,
Wakana
1 Like
Hi @Ryan_Johnson , Did the solution suggested by Wakana work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
1 Like