Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
markp201
Contributor III

Can AGGR dimension be dynamic?

I have a pivot table with a linear guage (bar) for the measure.

I have three dimensions: region, manager, salesrep

MAX(AGGR(sum(sales),salesrep)) yields the max metric across all employees but I want the max for salesrep under the manager under the region.

Same for the other dimensions.  If the pivot only shows region, the max should be for the region, not the sales rep.

I've tried a few variations with dimensionality

MAX(AGGR(sum(sales),PICK(DIMENSIONALITY(),region,manager,salesrep)))

but this often resulted in Max Value appearing on all the rows.

If there is only one dimension its simple but what needs to be done for multiple dimensions ?

5 Replies

Re: Can AGGR dimension be dynamic?

How do you decide your dimension? is it a cycle or drill-down group?

markp201
Contributor III

Re: Can AGGR dimension be dynamic?

Neither - its a pivot table with 3 dimensions.  The pivot table does the drill-down.

robin_hausdoerfer
Valued Contributor III

Re: Can AGGR dimension be dynamic?

I had the same problem but didn't find a solution...

For cyclic groups you can use getcurrentfield(), but  for dynamically expanded / collapsed pivots this is not possible I think.

romansavchuk
New Contributor III

Re: Can AGGR dimension be dynamic?

Did you try to move Pick with Dimentionality out of aggr function?


PICK( DIMENSIONALITY(), MAX(AGGR(sum(sales),region)), MAX(AGGR(sum(sales),manager)), MAX(AGGR(sum(sales),salesrep)) ).


I know that it`s not best solution from repeating code point of view, but maybe it`s solve your problem.

MVP
MVP

Re: Can AGGR dimension be dynamic?

I would think that this might be want you want to get calculated:

Pick(Dimensionality()

, Max(TOTAL Aggr(Sum(Amount),Region,Manager,SalesRep))

, Max(TOTAL <Region> Aggr(Sum(Amount),Region,Manager,SalesRep))

, Max(TOTAL <Region,Manager> Aggr(Sum(Amount),Region,Manager,SalesRep))

)

Or if you want the total sum per dimension and the max amount for the dimension :

Pick(Dimensionality()

, Max(TOTAL Aggr(Sum(Amount),Region))

, Max(TOTAL <Region> Aggr(Sum(Amount),Region,Manager))

, Max(TOTAL <Region,Manager> Aggr(Sum(Amount),Region,Manager,SalesRep))

)