Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
yes [Manager]= will ignore the selection.
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
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.
Hi Jonathan:
Thank you very much.
PC
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])
)
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