topic Re: How to select from an alternative set (e.g a bookmark) within an aggr function. in QlikView

How to select from an alternative set (e.g a bookmark) within an aggr function.

Thu, 31 Oct 2013 10:12:15 GMT

I am trying to select an alternative set (a bookmark) within an aggr with no luck. This does not work:

=sum(aggr(sum({BM2012<FiscalYear=,Year=,LOS = {">= $(vSlider)"}>} Distinct LOS), zNumber))

BM2012 is a bookmark for the fiscal year 2012-2013.

Re: How to select from an alternative set (e.g a bookmark) within an aggr function.

swuehl — Thu, 31 Oct 2013 10:24:54 GMT

You might need to apply the set expression also to the outer aggregation:

=sum({BM2012<FiscalYear=,Year=,LOS = {">= $(vSlider)"}>} aggr(sum({BM2012<FiscalYear=,Year=,LOS = {">= $(vSlider)"}>} Distinct LOS), zNumber))

Could be also something different that causes your issue, but 'this does not work' is not a sufficient piece of information to really help you.

Re: How to select from an alternative set (e.g a bookmark) within an aggr function.

Thu, 31 Oct 2013 10:26:38 GMT

That is the right way to do: use the bookmark name into the set analysis. if no space, no need to use any bracket.

I am surprised with the Distinct qualifier into the sum() function

Fabrice

Re: How to select from an alternative set (e.g a bookmark) within an aggr function.

Thu, 31 Oct 2013 13:35:26 GMT

Thanks for the responses.

Swuehl, this gives me a figure double of what i expect to see.

You might need to apply the set expression also to the outer aggregation:

=sum({BM2012<FiscalYear=,Year=,LOS = {">= $(vSlider)"}>} aggr(sum({BM2012<FiscalYear=,Year=,LOS = {">= $(vSlider)"}>} Distinct LOS), zNumber))

Could be also something different that causes your issue, but 'this does not work' is not a sufficient piece of information to really help you.

• Reply • Like Show 0 Likes(0)

I have only used a Bookmark as a workaround for the problem. The key requirement is to be able to select from a set, in this case fiscalyear '12-13', regardless of what the user selects for FiscalYear or Year fields on my app. So for example this function works:

=sum({<FiscalYear={'12-13'}, Year=,LOS = {">= $(vSlider)"}>} LOS )

But when using the criteria in an aggr it it the value returned is substantially less than the query excluding the aggr function. I am expecting them to be the same. (I have remove the vSlider for simplicity.)

=sum(aggr(sum({<FiscalYear={'12-13'}>} Distinct LOS, zNumber))

Re: How to select from an alternative set (e.g a bookmark) within an aggr function.

swuehl — Thu, 31 Oct 2013 14:05:42 GMT

It's almost impossible to help you, just because we still know almost nothing about your data and setting.

Could you upload a small sample file?

Your two expressions differ not only in the use of advanced aggregation, but also in the field modifiers used (e.g. Year= ) and in the use of a DISTINCT qualifier. So I wouldn't be surprised getting a different result.