topic Re: AGGR on Non-Explicit Dimensions in QlikView

AGGR on Non-Explicit Dimensions

Wed, 24 Feb 2016 20:06:18 GMT

New to Qlikview and have been stuck on this problem:

I'm making a scatter chart with the "Product" as the dimension, "Price" as the x-axis, and "Speed" as the y-axis. The problem I'm having is for the size of the bubble. For the size of the bubble I want the sum of the averages of each month. So for example, the size of the boat bubble would be:

Jul: 12+15 = 27 / 2 = 13.5

Aug: 50 + 60 = 110 / 2 =55

Sep: 20

13.5+55+20 = 88.5

I've tried this for the bubble size expression:

AGGR(

SUM(

AGGR(

AVG(Quantity)

, Product

, Month

)

, Product

)

But this doesn't work. I'm thinking it's because "Month" isn't set as a dimension so it errors out somewhere? So somehow I need to be able to use AGGR with dimensions that aren't explicitly set in the Dimensions tab of the chart object. Any help would be greatly appreciated.

Assume this data:

Product	Speed	Price	Month	Quantity
Bike	Slow	Low	July	12
Bike	Slow	Low	July	15
Bike	Slow	Low	August	50
Bike	Slow	Low	August	60
Bike	Slow	Low	September	20
Car	Fast	Medium	July	10
Car	Fast	Medium	July	10
Car	Fast	Medium	August	12
Boat	Moderate	High	July	14
Boat	Moderate	High	August	5
Boat	Moderate	High	August	6
Boat	Moderate	High	August	6
Boat	Moderate	High	September	6
Boat	Moderate	High	October	4
Boat	Moderate	High	October	5

Re: AGGR on Non-Explicit Dimensions

maxgro — Wed, 24 Feb 2016 20:48:52 GMT

PFA

Re: AGGR on Non-Explicit Dimensions

Wed, 24 Feb 2016 21:02:44 GMT

Wow I think that's what I'm looking for. I'm running person edition and can't open up your file (working on getting a license). Any chance you could explain what you did? Sorry!

Re: AGGR on Non-Explicit Dimensions

johnw — Wed, 24 Feb 2016 21:08:27 GMT

So that you don't have to wait, it looks like all they did was remove the outside aggr() from your expression, leaving:

    SUM(
          AGGR(
              AVG(Quantity)
              , Product
              , Month
          )
    )

Re: AGGR on Non-Explicit Dimensions

Wed, 24 Feb 2016 21:14:24 GMT

Thanks everyone!