Goal: I want to look at the percent split of the Id across comp_name on every updatedat(Date)
Problem1:
Now when I add a filter to see contribution from only comp_name ‘A’…It shows 100 percent since the Denominator is also filtered to only comp_name ‘A’
Problem2:
And I cannot use PRE_FILTER in Denominator:
distinctCountOver({ID},[{updated_at},{comp_name}],PRE_AGG)/distinctCountOver({ID},[{updated_at}],PRE_FILTER)
because when I put filter on other fields like to view only few group labels…then since Denominator is pre_filter it does not filter out the group labels and hence giving wrong percent.
You change the filters you need into anifelse statement when using PRE_FILTER and these would exclude non matching items from your denominator while including ones you need… Example:
Hey @darcoli ,
Thanks for the reply.
Yes, I have a multiple value selection for Group label use case.
This won’t work because I have alot of values under group label and creating fields for each is not feasible and it is a multi select option too.
Please let me know if there exists a workaround for this.
Yes, I have a multiple value selection for Group label use case.
This won’t work because I have alot of values under group label and creating fields for each is not feasible and it is a multi select option too.
Please let me know if there exists a workaround for this.
Hi @Ummehaani, per_filter in window function only provide flexibility of the calculation being impacted by all filters or no filters in the visual (except top/bottom N filters, according to the order of evaluation of QS).
For your question, I aggree with @darcoli that having the ifelse() function nested in the LAA function is the solution. And you can link the condition with a parameter (such as ifelse(Group_label=${parameterGroup}…) to use the parameter control to trigger the filter.
We currently have the limitation of using only single select parameter in calculated field, so you have to filter on the group one by one to see the result. Good news is that we are working on the IN/NOTIN calculation function to consume multi-select parameters. When that feature is enabled, you will be able to link a multi-value parameter to filter on the result while not being impacted by the visual filter.
@emilyzhu
The in/notin condition does not work with a parameter, as quicksight requires parameter to have a default value. is there a work around this?
In my use case, I want my pre_agg sum over measure to apply all filters from control except the date filter.
