Hi, I’m looking for a way to filter visuals based on the current value in a text field:
I have a dataset with daily customer usage data. Each row holds daily aggregations of a customer’s usage and the customer’s status and account owner for that specific date.
Naturally, ‘status’ and ‘account owner’ are textual fields and they can change over time (for example, when a customer stops paying or changes owner).
I’m looking for a way to filter visuals based on the value of these text fields on the latest date (yesterday).
I thought of creating a calculated field that will always populate the value based on the latest date- but I have no idea which formula to use.
would love to hear your thoughts.
Thanks!
Hello @Yuval.Shiboli, is your latest date always going to be yesterday? If so, you can apply a date & time range filter to your visuals where you have 2 rolling dates. One for start of previous day and the other for end of previous day. That way it will always target yesterday.
For filtering your text values, you can create a parameter for both fields. Then add a control for each that you can connect to the dataset field to display the options for Status and Account owner. Once your controls are built out, you will need to filter the Status and Account Owner fields on your visuals to equal the parameter value that is set. That will let users check for specific statuses from the latest date.
I’ll include some links below for details on date filtering and filters from parameter based controls. If this helps, please mark my response as a solution, otherwise let me know if you have any follow-up questions on this topic.
Hi @DylanM , thanks for reaching out. I think my explanation was not clear enough… let me try again with an example.
if this is my data set:
date
account_name
success manager
impression_count
Jan 16, 2024
Acme
Yael Lobel
5,205,014
Jan 16, 2024
Disney
Avi Borgen
30,477,192
Jan 15, 2024
Acme
Avi Borgen
4,943,227
Jan 15, 2024
Disney
Amir Alon
27,440,706
Jan 14, 2024
Acme
Avi Borgen
5,552,004
Jan 14, 2024
Disney
Amir Alon
24,355,379
Jan 13, 2024
Acme
Avi Borgen
4,487,121
Jan 13, 2024
Disney
Amir Alon
21,991,266
I want to automatically filter the table to show me, on any given day, the daily impression count of customers currently owned by Avi.
So the result should be:
date
account_name
success manager
impression_count
Jan 16, 2024
Disney
Avi Borgen
30,477,192
Jan 15, 2024
Disney
Amir Alon
27,440,706
Jan 14, 2024
Disney
Amir Alon
24,355,379
Jan 13, 2024
Disney
Amir Alon
21,991,266
For that, I thought of creating a calculated field that will return the current success manager.
Hope it is clearer now
Thanks in advance for your support
Hello @Yuval.Shiboli, okay, I understand now. Basically, you are going to want to use the lastValue function in QuickSight.
You will want to sort it by date and partition by account_name to receive the most recent success manager. Than instead of filtering your visual directly based on the parameter value being set in your control with success managers, you will check if the success manager returned is equal to the lastValue success manager. If that is true, return the success manager, else NULL. Then you can filter your visual based on your calculated field to exclude null values.
I will mark this as the solution, but if you have follow-up questions on implementing this, please let me know!
Hi @DylanM , I stumbled on the filtering part…
I have a dashboard with line charts tracking different KPIs over time. each time I try to apply a filter on the line carts with the new current CSM calculated field I get a VISUAL_CALC_REFERENCE_MISSING error.
Any ideas where to go from here?
The visual is set to track the Block_Count value over time, with the Account Name dimension as colors and the Old CSM dimension as small multiples.
While I managed to filter the visual successfully by the current CSM value, I wasn’t able to use the New LastValue CSM as a dimension because: “custom aggregation field is not allowed as a dimension”
My end goal is to run anomaly detection alerts on this visual, so I need to find a way to track the Block_Count value over time on a single/unified line chart.
any idea how to workaround this obstacle?
Hello @Yuval.Shiboli, if you are unable to utilize that calculated field we created above in the visual, then my recommendation would be to use custom SQL on your dataset to build that last value function as a column on ingestion. That will ensure that you will not run into any aggregation issues within QuickSight. It will depend on the database you are using to query the data you are adding into QuickSight, but the Last Value function should function similarly in SQL. I will include some documentation below:
