Is there any way at all to be able to set a minimum and maximum value in a filter based on the values of a calculated field? It doesn’t have to be a slider, it can be anything, but the user has to be able to control it. Are you saying there’s no way to filter a pivot table by a range of values? How else do I go about this?
For all the convoluted workarounds in quicksight, I’m genuinely shocked to hear there is no way method of filtering from the minimum or maximum values in a field?
Immediately archiving this post without answering my question, confirming if it was understood (it wasn’t), and without providing any clarity on a solution wasn’t helpful so I appreciate taking the time to answer.
I understand the pain point in this scenario, unfortunately the potential work arounds when dealing with dynamic controls is light since there are set restrictions in place, especially with controls and parameters.
In terms of setting a Min and Max for filtering, this can easily be achieved by setting up two parameters, one for Min and one for Max (a text field would likely be the most sufficient so that you’re not listing hundreds of values). Then set up a filter on your visual (has to be integer field) using the ‘between’ condition:
Additionally, you then have to link it to another control (which will require you to select a field from your dataset…not a dynamic value option)
I understand the frustration here, as having min and max controls that can be set off various fields, not just one, would be extremely useful which is why I tagged it as a feature request. The feature request does not process on the support side’s system without a topic being ‘closed’ in some capacity.
If there are any additional questions, we can use this thread to continue
Hello @C_B, to follow up the previous comment, I took some time to try and figure out a potential work-around for setting the filters. It will require some tweaking, but we can try to debug a bit.
My thought was, we could set the parameters with Min and Max values based on calculated fields with a navigation action.
I created a “button” with a table visual that can set the parameters with the calculated fields I created. The user can select that button and then it will populate the values. The hardest part will be figuring out how we want that value to be filtered/not filtered by the sheet.
Now, the slider is really where we hit a roadblock. We can’t use a 2 value slider with the parameters. I set the filters as 2 different text boxes for now so the user would be able to edit the filters. Also, you will want to have an alternative button. In my example I just called it “Reset to Default”. This will be the place where you want to reset any parameter filters and the min and max parameters so the user can control what we will calculate min and max based on.
It isn’t perfect, but I think this can get you closer to a potential solution.
Thank you for explaining. Can I confirm what you mean when you say “set off various fields, not just one”? Revenue Per Account and Transactions Per Account are separate value fields that will always be in the pivot table. They will each need their own filter(s), and I probably should have only shown one in my screen shot to make that clearer. The only thing that the user can change is the grouping combinations (ex age, age decade, gender, etc). Does this change the guidance?
And thanks for explaining that the closed issue gets it to a dev team!
@DylanMorozowski Thank you! I’ll try this right now. When you say two value slider, do you mean two different value fields using one control? I expect there will be two different filters, one for each field. Those value fields columns will both be in the pivot table and not change. The values within them will be recalculated when the user selects different groupings or applies filters.
Hello @C_B, I am specifically referring to controlling the Min and Max values with the slider control that allows the user to adjust the Min Value and Max Value on a single control. I think you understand what I am saying, just wanted to make sure and clarify.
Min and Max can be adjusted by the user from 2 different controls. Those will apply to the parameter values that will then filter the visual. We may need to apply the filter differently if the value field in the pivot table can be replaced with different fields. If we are just changing the group by fields, the filter can be applied as is in my example.
@DylanMorozowski Ok this is a great start and super helpful. The good news is that the filter for min & max with the text field works just fine. However I can’t get the minOver and maxOver to work with a PRE_AGG, so I need to debug that.
I created two calculated fields. (These use the parameters, but I also used the actual field names [{Attribute 2}, {Attribute 1}] with the same results.)
_Set Min Revenue Per Account = minOver({Revenue per Donor}, [${Attribute2}, ${Attribute1}])
_Set Max Revenue Per Account =
maxOver({Revenue per Donor}, [${Attribute2}, ${Attribute1}])
and got this error message:
“For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated”. I think this is because {Revenue per Account} creates an average. I removed the calculation level which defaults to POST_AGG_FILTER. Now the value is the overall Revenue per Account, not the actual min and max values. I included the Total Row here to show the amount.
Hello @C_B, for this functionality, you are going to want to avoid aggregations. That will require either PRE_AGG or PRE_FILTER
We will want to adjust the {Revenue per Account} calculation, instead of the minOver/maxOver functions. If it is an average, you should be able to convert to an averageOver function instead, then the brackets will contain the group by values you want the averageOver function to utilize.
@DylanMorozowski the Revenue per Account calculated field requires a distinct count due to the underlying data Revenue Per Account = sum({Transaction Amount})/distinct_count({Account ID}).
The avgOver() function doesn’t seem to allow for a distinct count. I get the same error about not using aggregated operands with PRE_AGG for avgOver({Transaction Amount}, [distinct_count({Account ID}], PRE_AGG)
I’d like to put that as a feature request as well.
In the interest of time, we went with your and @JacobR‘s solution to make a text controls for minimum and maximum which are good enough for my deadline. We won’t do the KPI portion for now. We’ll revisit the dynamic defaults if/when qs slider controls can accommodate this functionality. Thanks for your help!
The various fields comment was in regard to wishing there was an ability to set a min and max value from various fields like if you had multiple columns in a table visual. This does not change the guidance I shared as being about to have min and max controls set off by various fields is not currently possible.
Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found another solution in the meantime.
If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.
