topic Re: Set Analysis Doesn't work / Aggr in Sum in QlikView

Set Analysis Doesn't work / Aggr in Sum

vkozlov — Thu, 23 Jun 2016 12:38:38 GMT

Hi all!

Today I faced a problem:

Set Analysis Doesn't work, when i use Aggr function in Sum function.

Example:

=Sum({<Year={2016}>} Aggr(Budget,Segment,Region, MonthYear))

(budget loaded only on 2016)

Looks like:

Without Year Selection - ok result "3618810433,6481" (dada loaded only on 2016)

With selected Year=2016 - ok - result "3618810433,6481"

But if we select other Year (2015 for example)

We will get "0" as result.. It means, that our Set in function Sum({<Year={2016}>} Aggr(.. doesn't work. The result must be "3618810433,6481"

Help me somebody.. Why [Set Analysis Doesn't work / Aggr in Sum]?

sunny_talwar — Thu, 23 Jun 2016 13:54:48 GMT

Try this:

=Sum({<Year={2016}>} Aggr(Only({<Year = {2016}>}Budget),Segment,Region, MonthYear))

Anonymous — Thu, 23 Jun 2016 23:30:56 GMT

Instead of Only, it might be better to use nested sum. That way Aggr will do a row reduction and improve expression performance