topic Re: MAX() / AGGR() Questions in QlikView

MAX() / AGGR() Questions

Fri, 09 Jan 2015 06:11:16 GMT

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.

Re: MAX() / AGGR() Questions

jonathandienst — Fri, 09 Jan 2015 06:15:07 GMT

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

HTH

Jonathan

Re: MAX() / AGGR() Questions

Fri, 09 Jan 2015 06:18:25 GMT

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.

Re: MAX() / AGGR() Questions

SunilChauhan — Fri, 09 Jan 2015 06:23:18 GMT

yes [Manager]= will ignore the selection.

Re: MAX() / AGGR() Questions

jonathandienst — Fri, 09 Jan 2015 06:25:39 GMT

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

Re: MAX() / AGGR() Questions

tresB — Fri, 09 Jan 2015 06:28:19 GMT

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.

Re: MAX() / AGGR() Questions

Fri, 09 Jan 2015 06:31:56 GMT

Hi Jonathan:

Thank you very much.

Re: MAX() / AGGR() Questions

SunilChauhan — Fri, 09 Jan 2015 06:33:02 GMT

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])

)

MAX(

AGGR(

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

)

Re: MAX() / AGGR() Questions

Fri, 09 Jan 2015 06:33:33 GMT

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.