Aggr function not respecting set analysis exclusions

[SAM81]

Hi

I have a dimension for a chart using the following function:

Aggr(NODISTINCT Sum({<WeekCommencing,[Recorded Date],[Correlated Group],CorrelCode,[Correlated Search]-=>}[Handled]), WeekCommencing)

I need this to not be impacted if the user makes a selection from any fields in the set analysis expression: - {<WeekCommencing,[Recorded Date],[Correlated Group],CorrelCode,[Correlated Search]-=>}

Normally, the above would work fine, but for some reason when including in a Aggr function it does not.  

What am I missing?

[Oleg_Troyansky]

Hmm, I'm not sure I understand what you mean by this syntax:

[Correlated Search]-=

Typically, '-=' would mean "exclude these values". No value list means "all possible values". So, your Set Analysis requires to exclude all possible values in this field - is that what you had in mind?

Another question I have is this - is it the whole formula, or do you also add another aggregation on top of your AGGR() ? If you do, then it's a totally different story.

If you'd like to learn everything there is to learn about Set Analysis and AGGR, consider joining our Masters Summit for Qlik, coming to Madrid and to New Orleans this fall, where I teach a session on "Set Analysis and Advanced Aggregation". Perhaps we could discuss this issue in class.

https://masterssummit.com/

Cheers,

[SAM81]

Thanks Oleg

The full formula is as follows.

Sum({<[Recorded Date],[Correlated Group],[Correlated Search],CorrelCode,WeekCommencing-=>} Hits) / Aggr(NODISTINCT Sum({<WeekCommencing,[Recorded Date],[Correlated Group],CorrelCode,[Correlated Search]-=>}[Handled]), WeekCommencing)

The first half Sum({<[Recorded Date],[Correlated Group],[Correlated Search],CorrelCode,WeekCommencing-=>} Hits) is respectful of the selections I want to exclude.

The second half is not.  

Aggr(NODISTINCT Sum({<WeekCommencing,[Recorded Date],[Correlated Group],CorrelCode,[Correlated Search]-=>}[Handled]), WeekCommencing).

[Oleg_Troyansky]

Now I have even more questions than answers:

- Still puzzled about the meaning of the "-=" filter

- The Sum in the numerator returns a single value, while the AGGR() in the denominator returns an array of values - one value for each possible value of WeekCommencing. Unless the chart dimensions always render a single value of WeekCommencing, you will likely get a NULL value in response. On the other hand, you are trying to ignore user selections in the field WeekCommencing. This filter wouldn't quite work in a chart with a dimension that's related to WeekCommencing. Maybe a TOTAL qualifier could help you break out of the dimensionality, but you still need to enclose your AGGR() in some form of an aggregation function, like a Sum() or an Avg(), to ensure that you are getting back a single value and not an array.

Perhaps I could prescribe better if I could understand what you are trying to achieve by this calculation.

Cheers,