topic Calculated dimension in aggr in QlikView

Calculated dimension in aggr

Wed, 04 Sep 2013 15:27:31 GMT

Hello,

is it possible to have aggr() do roll up over a calculated dimension? Something like:

=aggr(sum(Sales), Year(Date))

It looks like aggr breaks the moment I put any expression as dimension. For example,

this works: aggr(sum(Sales), Date)

and this doesn't: aggr(sum(Sales), if(1=1,Date))

Any help would be appreciated!

Re: Calculated dimension in aggr

DavidFoster1 — Wed, 04 Sep 2013 15:47:03 GMT

I dont think that would work as the aggr statement will not have something solid to query the data model with.

Does the calculated dimension have to be calculated on the front-end or could it be pushed back to the data model?

Re: Calculated dimension in aggr

whiteline — Wed, 04 Sep 2013 15:58:03 GMT

Hi.

There is a note about calculated dimensions in help:

There is a special function for advanced aggregations:

aggr ([ distinct | nodistinct ]
[{set_expression}]expression {, dimension})

Returns a set of values of expression calculated
over dimensions. The result can be
compared to the expression column of a 'local chart', evaluated in the context
where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).

...

Re: Calculated dimension in aggr

Anonymous — Wed, 04 Sep 2013 16:17:37 GMT

... That is, the field Year must be created in the script. After that, this will work:

aggr(sum(Sales), Year)