Aggr Function- QV 11

[#1]

Hi All,

I was hoping someone could help me with the Aggr Function. I have QV 11 version. It appears the function does not work in the script.
Nevertheless, I try to create the function as an expression in a straight table. I have the expression "

Aggr(Sum (ACTUAL_CASH), MERCH_DESC)

My understanding  is that this function would sum cash at each merch_desc level. It appears to not be working. Each level comes to the same total sum.

Any advice? I have some screen-shots attached.

@ andrew.carroll

[hic]

The Aggr() function creates an array, so you cannot use it the way you do. Either you use

     Sum(Aggr(Sum (ACTUAL_CASH), MERCH_DESC))

or you use

     Sum (ACTUAL_CASH)

Usually, it is the latter one that people need.

See more on this blog post.

HIC

[#3]

Ok thanks. Sum(Aggr(Sum (ACTUAL_CASH), MERCH_DESC)) did not work in the straight table.

And I don't think the AGGR function is available in the script load.

When I do just Sum (ACTUAL_CASH) it shows at a different level than i want.

[Clever_Anjos]

AGGR is a function you use to build expressions.

In script time, you should use LOAD ... FROM .... GROUP BY statement

[Oleg_Troyansky]

Andrew,

AGGR is a tricky function, especially when used in charts, because it modifies the default aggregation level, and you need to truly understand what's going on in order to make it work the way you want it.

In charts, basic aggregation such as sum(Cash) will aggregate the expression to the level of the Chart dimension - for example, a chart showing "Cash by Account" will aggregate Cash by the Account level, if you picked Account as your dimension. So far, so good.

Now, a function like sum(AGGR(sum(Cash), Merchant)) will aggregate Cash by Merchant (in a virtual array) and then, if it's used in a chart, QV will further aggregate the results to the level of the Chart Dimensions.

Now, here is the caveat - if your AGGR dimensions are less granular than your Chart dimensions, then you'll get wrong results (picture yourself bucketing data by Product and then trying to show it by Customer).

So, as a rule of thumb, - in most cases your AGGR dimensions should be always more granular than your chart dimensions. Some exceptions exist, and they involve using keywords like DISTINCT or NODISTINCT.

All the different aspects of AGGR are certainly bigger than a single post can hold, and they deserve to be documented and trained on better than they are now.

I teach a 4-hour class on Advanced AGGR and Advanced Set Analysis, as a part of the Masters Summit for QlikView. You may want to look into it, if you are interested in learning all the tricks about AGGR.

best,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com