MAX() / AGGR() Questions

[#1]

Hi All:

I have been struggling with this for a long time. Hope you guys can help.

Here is a mock up example:

Let's say I have different sales agent in different division. Each sales agent has one manager. Agent could be Active or Inactive.

I am trying to calculate the maximum average sales per sales agent by division for active agents only in the last 12 months which is basically (Total Sales/Total Headcount)

And I also want the value to be unchanged when I make a selection on Managers.

I have an expression like this:

=

MAX(

AGGR(

SUM( {$<[Agent Status] = {'Active'}, [Manager]=, [Quick Calendar] = {'LTM'}>} [Sales])

/

COUNT(DISTINCT {$<[Agent Status] = {'Active'}, [Manager] =, [Quick Calendar] = {'LTM'}>} [Agent Name])

,[Agent Division])

)

This works fine when I have no selection on Manager.

But when I make a selection on Manager, the value changes. I thought after I adding '[Manager]=,' in the set analysis would make the expression ignore selections on [Manager]. I cannot find any patterns when the number change.

I have also tried changing '&' to '1' for the set analysis identifier but it doesn't work as well.

Thank you very much.

PC

[jonathandienst]

Hi

I suspect that selecting manager is reducing the possible values in another field that is affecting your calculation - for example [Agent Division].

HTH

Jonathan

[#3]

Hi Jonathan:

Thank you very much for your quick reply.

Do you have any suggestion for solving this problem?

Also does '[Manager]=' in set analysis ignore selections?

Thank you.

PC

[SunilChauhan]

yes  [Manager]= will ignore the selection.

[jonathandienst]

PC

Yes, Manager= will cause the manager selection to be ignored in the Sum()/Count() expressions.

To get suggested solutions, upload a sample of your model that shows the problem.

Jonathan

[tresesco · Accepted solution]

Try including set analysis in the max() as well like:

Max({<Manager>} Aggr(....

Yes, [Manager]=, ignores the selection, but that is only for the aggregarion fucntion it is within. If you select a manager, aggr and max still consider the selection and that would cause the value change.

[#7]

Hi Jonathan:

Thank you very much.

PC

[SunilChauhan]

MAX(

AGGR(

SUM( {$<[Agent Status] = {'Active'}, [Manager]=, [Quick Calendar] = {'LTM'}>} [Sales]),[Agent Division])

/

Aggr(COUNT(DISTINCT {$<[Agent Status] = {'Active'}, [Manager] =, [Quick Calendar] = {'LTM'}>} [Agent Name])

,[Agent Division])

)

or

MAX(

AGGR(

Avg( {$<[Agent Status] = {'Active'}, [Manager]=, [Quick Calendar] = {'LTM'}>} [Sales]),[Agent Division])

)

[#9]

Hi Tresesco:

Thank you very much for your advice.

It works fine after adding '[Mananger] =' in the set analysis of MAX().

I didn't know MAX() has set analysis before.

Thank you so much.

PC