topic Can AGGR dimension be dynamic? in QlikView

Can AGGR dimension be dynamic?

markp201 — Thu, 07 Jun 2018 17:38:00 GMT

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 ?

sunny_talwar — Thu, 07 Jun 2018 17:41:04 GMT

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

markp201 — Thu, 07 Jun 2018 17:56:06 GMT

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

Anonymous — Thu, 07 Jun 2018 18:33:37 GMT

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 — Tue, 26 Jun 2018 07:38:10 GMT

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.

petter — Tue, 26 Jun 2018 16:20:11 GMT

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

)