How do filters inside and outside of an aggregation work?

[Thomasowich]

I have a measure

SUM({<Type = {'A'}>} Aggr(<some measure>, Customer, Type)),

which changes when I filter on Type = 'A', and which is different to

SUM({<Type = {'A'}>} Aggr({<Type = {'A'}>} <some measure>, Customer, Type)).

I do not understand why these to formulas are different, or why the first one changes under additional filtering. My understanding was that Aggr(..., Customer, Type) works like a table with two dimensions as columns and the measure as a third column. But this seems to contradict the above behaviour. 

[marcus_sommer]

Each part is independently from each other evaluated against the selection state. This means here:

SUM({<Type = {'A'}>} Aggr(<some measure>, Customer, Type))

the inner part has no condition and will include all possible values and the wrapping outer part has a condition which comes on top of the inner part and will reduce it.

In other words - each part may need an own condition to get the indented result - it's not really a choice to place it anywhere. Personally I put the essential conditions at the most inner place and only if it's necessary I add further ones in the outer parts.

[Thomasowich]

Thanks for your answer!

But I still don't understand: The inner part has no condition, but calculates the measure for each (customer and) type - so summing over Type = 'A' outside should only get those "rows" of type A, where the measure is calculated as if there was a filter on type A. Is that not correct?

[martinpohl]

I think, in that case it would work because field type is part of the aggregation. Then only values from type A would be summed. 

but as Marcus mentioned, if you do a caluclation inside the aggr-function you only could refer to fields that are linked to the field from the aggregation (e.g. country from customer) but not unlinked fields (e.g. material group or billing type)

Regards

Martin

[marcus_sommer]

The order of the conditions-evaluation is like:

sum({< ThirdCondition >} aggr({< SecondCondition >} count({< FirstCondition >} Value), Dim1, Dim2))

whereby each condition goes against the selection state - but excluded values from a more inner part remain excluded even if the outer conditions would allow them.

[Thomasowich]

That was also my understanding, but the two versions, both filtering on an aggregation-dimension, give different result, as stated in my question. I still don't understand why.

[Thomasowich]

Yes, but I get different results applying the same condition inside (<SecondCondition>) that I apply outside of the aggregation (<ThirdCondition>). 

[marcus_sommer]

Please provide a small show-case which demonstrate the behaviour. Maybe one or two small inline-tables with around ten records and then how looked the expression and which differences are occurring between the various set analysis places. Further where are the expressions been used and which results are you considering as correct and which not.

[Thomasowich]

Unfortunately, it is a complicated measure, and I have not yet been able to recreate this with sample data. 

I have noticed that SUM({<Type = {'A'}>} Aggr({<Type = {'A'}>} <some measure>, Customer, Type)) may also change when the user filters on Type = 'A'. 

[marcus_sommer]

If there are further expressions and/or calculated dimensions included (directly or per variables or maybe as a master-item) in the object they may react on the selections.

Further take a look if any alternate states exists - maybe the object and selections are in different ones.

Also important is the data-model. If there are any circular loops or synthetic keys they should be resolved. Beside this the association between the data may not be suitable for your wanted view - especially by missing keys and/or NULL's within the related fields. Ideally the data-model has a star-scheme and NULL is replaced with a real value like '<NULL>' to make them visible and accessible.