topic Re: Aggr function inside Set Analysis in QlikView

Aggr function inside Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to find the sum of the last day's value over a specified date range. This means finding the sum of the value on the last day of the week, month, year or quarter. The date range is variable, and set by the user, i.e. they click monthly and the variable is set to the dimension needed.

I've created an aggregate function to determine the last day of the time interval period, and it seems to be working fine, it returns the correct values (last day of the week/month/quarter/year).

Function: =AGGR(MAX(DATE_ID),$(vTimeFunction))

Now my problem, I'm trying to use this aggr function inside set analysis, to get the sum of the value where the day = aggr value, but no luck. The set expression returns the value of a seemingly random day in the period.

Set Analysis: =SUM({<DATE_ID = {"=AGGR(MAX(DATE_ID),$(vTimeFunction))"}>} VALUE)

Any ideas on how to do this correctly, or what is causing my set analysis not to work?

Help is greatly appreciated!

Re: Aggr function inside Set Analysis

swuehl — Thu, 15 Sep 2016 16:02:35 GMT

An advanced search is creating internally an hypercube with DATE_ID as dimension and

=Aggr(Max(DATE_ID), $(vTimeFunction) )

as expression.

The dimension values where the expressions return true are included in the set.

It's not a good idea to use aggr() as expression without an outer aggregation function:

Pitfalls of the Aggr function

Use Aggregation Functions!

Maybe it's better to create a couple of flags in your master calendar and chose which flag to use using your variable.

Re: Aggr function inside Set Analysis

bertinabel — Thu, 15 Sep 2016 16:17:28 GMT

Creo que para lo que necesitas no es necesario el uso de AGRR(), intenta con el Set analysis como se muestra acontinuación, espero te sea util.

Set Analysis:=Sum({$<date_id =, date_id ={"$(=Max(date_id ))"}>}VALOR)

Saludos.