topic Re: aggr working on version 11 but not 12 in QlikView

aggr working on version 11 but not 12

Anonymous — Mon, 27 Jun 2016 10:47:42 GMT

Hi guys

The following is working perfectly in version 11, but not 12. Please help.

The expression is:

=min(aggr({<AsOfDate={'>=$(vMinDate)<=$(vMaxDate)'},FromDate={'>=$(vMinDate)<=$(vMaxDate)'}>} exp(sum(log(1+Return)))-1,PortfolioName,FromDate,AsOfDate))

The variables are defined below:

vMinDate: Max(TOTAL aggr(Min(ReturnDate),PortfolioName))

vMaxDate: Min(TOTAL aggr(Max(ReturnDate),PortfolioName))

Thanks.

Joao

Re: aggr working on version 11 but not 12

jonathandienst — Mon, 27 Jun 2016 11:06:18 GMT

Are you sure this works in QV11.2? Because it looks syntactically incorrect - AFAIK Aggr() does not support set expressions directly, they need to be in an aggregation function such as Min(), Sum(), Avg()....

Re: aggr working on version 11 but not 12

Anonymous — Mon, 27 Jun 2016 12:20:02 GMT

I see what you mean, and it certainly underlines many parts of the expression for this reason (suggesting there is a problem), but it has always worked. I've upgraded a couple of installations to version 12, and on these it is not working. The rest are still on 11.2, and it works perfectly (no changes i.e. we are all using the same models).

Re: aggr working on version 11 but not 12

Anonymous — Mon, 27 Jun 2016 12:21:50 GMT

And I've tried changing the expression so that it is syntactically correct with the exp(sum( straight after the aggr( so that the set analysis happens inside the sum, but it still doesn't work. The new expression is:

=min(aggr(exp(sum({<AsOfDate={'>=$(vMinDate)<=$(vMaxDate)'},FromDate={'>=$(vMinDate)<=$(vMaxDate)'}>} log(1+Return)))-1,PortfolioName,FromDate,AsOfDate))

Re: aggr working on version 11 but not 12

Anonymous — Mon, 27 Jun 2016 12:23:30 GMT

I've just removed the set analysis part out of the expression, and this works perfectly i.e. the expression is now:

=min(aggr(exp(sum(log(1+Return)))-1,PortfolioName,FromDate,AsOfDate))

Re: aggr working on version 11 but not 12

Wed, 03 May 2017 05:45:33 GMT

I feel like variables work differently in version 12+ and Qlik Sense than they do in previous versions...