Apply PRE_FILTER impact to only few columns - PRE_FILTER Enigma

Hey All,
I am struggling with LAA aggregation and Filters.

DataSet

Updatedat ID comp_name Group_label
Mar 28, 2022 4e93e512bb6c2c59809a52e9eeb2054a A Model
Mar 7, 2022 09746112950b51d53ebdca13e84bf96a A Others
Mar 7, 2022 136d05c363810dcfaaf86ffc86488c02 A Others
Mar 7, 2022 b1edb6c7fc8d9422faa4933fd7b6578f B Others
Mar 7, 2022 b685f2f81711f587138bdb9f8a2b0f9f B Others
Mar 14, 2022 09746112950b51d53ebdca13e84bf96a B Others
Mar 14, 2022 136d05c363810dcfaaf86ffc86488c02 A Others
Mar 14, 2022 b1edb6c7fc8d9422faa4933fd7b6578f A Others

Calculated Field:
Percent Field: distinctCountOver({ID},[{updated_at},{comp_name}],PRE_AGG)/distinctCountOver({ID},[{updated_at}],PRE_AGG)

Visual

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.

How do I solve this issue?

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:

distinctCountOver({ID},[{updated_at},{comp_name}],PRE_AGG)
/
distinctCountOver(ifelse(Group_label='A',{ID},null),[{updated_at}],PRE_FILTER)

You can also put a parameter instead of ‘A’ but will not work with multiple value parameters because these are not supported in calculated fields.

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.

Hey ALL,
Could anybody find a solution to 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.

1 Like

Hey @emilyzhu ,
Can I expect the in/not in feature anytime soon?

Hi @Ummehaani , we are going to work on the IN/NOTIN around Q4 2022.

1 Like