General question about aggr functionality

[Ori_Hait]

hey guys,

something that still has me baffled.

I average an aggregated metric over period of times represented by a line chart X axis (months, weeks etc...)

the formula is as follows

avg(
    aggr(
         Oss,
         assigned_to,month
         )
    )

Oss is master measure that represents the percentage where the "assigned_to" concluded the assignment without moving it to another assignee, and we aggregate it by month to display it on a line chart.

The set analysis inside the oss master measure has a limitation to a certain date flag and a certain date range which is controlled by a variable input.

I was getting slightly inaccurate results until I included the same set analysis in the "AVG" that wraps the Aggregated table.

the adjustment of the code looks like this

avg({same set analysis as is inside the oss master measure}
    aggr(
         Oss,
         assigned_to,month
         )
    )

I wonder if someone knows why this happens? perhaps it is related to order of execution or something of this sort?

thanks in advance!.

[marcus_sommer · Accepted solution]

Each calculation depends on the selection state in which it's performed. In your case you have two aggregations. The inner one is within the aggr() and has a set analysis which does any adjustments to the default selection state but the outer aggregation which is wrapping the aggr() has none separate set analysis and reacts therefore normally against the default selection state.

You will notice bigger differences if you selects any further values which are more in opposite to them which are specified within the inner aggregation which may lead to ZERO or an empty chart.

Essential by an aggr() is that it creates at first an invisible respectively virtual table - against then the specified aggregation (with or without any - set analysis - conditions) are performed which may beside the calculation results filter or extend the number of rows. The specified dimensions are the keys against the data-model. It's quite the same as if you had pre-aggregated the calculation within an own table in the data-model - and of course it will react on the applied selections.

Therefore what you have noticed is expected and the intended behaviour. 

[Ori_Hait]

thank you @marcus_sommer for your detailed explanation.