Filter based on Metric Based Aggr Dimesion

[chandrasjr1]

Hi All,

I have a metric defined as follows.

Metric_Flag= If(Metric1>0 and Metric2<10 and Metric3='XYZ', 'Yes','No')

When this is added into a table, Yes & No flag works fine. Now I want to make it as a filter so I can select what rows are making Yes or No.

So I converted the metric as Dimension using Aggr function.

Aggr(

If(Metric1>0 and Metric2<10 and Metric3='XYZ', 'Yes','No')

, Dim1,Dim2,Dim3)

Now when I filter Yes it individually filters Dim1 , Dim2 and Dim3 not the combination of Dim1.

E.g It selects Dim1 = Yellow, Orange

Dim2 = Square, Rectangle

Dim3 = XYZ

Instead it should select Yellow+Rectangle + XYZ rows and Orange+Square+XYZ rows.

As currently it selects Yellow & orange for DIm1 and Square and Rectangle for Dim2 the flag Yes/No is not filtered correctly.

Aggr(

If(Metric1>0 and Metric2<10 and Metric3='XYZ', 'Yes','No')

, Dim_key)

Now the filter also work. Only problem is, granularity of the table is user controlled. User can add or remove fields so total permutations are high that I can't create each Dim_key for every possible combination user would be selecting.

Is there a alternate method for Aggr to work correctly when filtered when it has multiple Dimensions? 

The temp workaround I made is to introduce a concatenated field DIM1&Dim2&Dim3 as Dim_key and use that in the Aggr function as follows.

[hic]

You want records that fulfill
(Yellow AND Rectangle AND XYZ) OR (Orange AND Square AND XYZ)

but you get
(Yellow OR Orange) AND (Rectangle OR Square) AND XYZ

This is how the Qlik engine works. To get around it, you need to have a field that uniquely defines Dim1, Dim2 amnd Dim3, like a primary key for all three. Like you do when you concatenate them. But otherwise it is not possible.

Take a look at https://community.qlik.com/t5/Design/Data-Reduction-Using-Multiple-Fields/ba-p/1474917 and you'll understand. The blog post is about Section Access, so it looks as if it is unrelated to your problem, but it is in fact the same problem.

[chandrasjr1]

Thank you. Currently I created Dim_key (unique key) which worked for one set of Dimension combination. Only problem is I have 10+ fields that users can Show/Hide in the report. So my granularity i.e. unique field combination  is always changing based on the user selection. So I might end up with multiple combination of unique_key fields to achieve this. 

E.g. one case DIM1 + DIm5 would be by unique key. Another case it could be DIm1+DIm3+Dim8 and so on.