AGGR Function(Performance Issue)

[#1]

Hi,

I have been using the AGGR function in qlik and had a question.

The expression i am using is AGGR(Nodistinct Count(Distinct USERS_META_ID),WEEK_ID,SEGMENT_1,SEGMENT_2).

So this expression creates a table in memory which has Count(Distinct USERS_META_ID) grouped at week,Segment_1 and Segment_2 level for all the 52 weeks and all the segments in data. The performance is poor.

Now my question is even if i filter only for 2 weeks and few segments, the data will be grouped for all the weeks and segments and then filtered for the required week and segments. So is there a way in which in the aggr function we can only group by the selected elements of week and segment rather than all the elements. This will mean that the table created will only have few weeks instead of many and may improve performance.

Thank You

[jonathandienst]

My understanding is that the Aggr() dimensions are filtered to the possible values, but I don't know if that happens before or after building the Aggr() table.

One would hope that it filters before building the Aggr(). I have not tested this rigorously, but my experience suggests that this is indeed the case.

Perhaps an expert from Qlik would like to comment here.

[#3]

Thank You Jonathan for the quick reply, i have a feeling filtering happens after the aggr() table is created, as even when i select 1 week and 1 element from the segment columns the report takes some time to execute. As you mentioned it would be great if an expert could confirm the behavior.

[marcus_sommer]

Are you sure that you need the NODISTINCT? Also if this aggr isn't a calculated dimension an outer aggregation like: sum(aggr(Function, Dim1, Dim2)) might be useful.

Another important influence for the performance (of each calculation) is from where the used fields come from - come they from a single table the performance will be significantly better as if the fields comes from different tables maybe connected over several table-hops and/or link-tables.

- Marcus

[#5]

Hi Marcus,

We need NODISTINCT as otherwise only few rows return values and remaining rows return null.

The fields USERS_META_ID, SEGMENT_1 and SEGMENT_2 come from a single table the other table contains WEEK_ID,USERS_META_ID and 80 other additional columns.

We are not combining everything in one table as the number of records will become too large.

Thanks You

[marcus_sommer]

Did you tried it with an outer aggregation and without NODISTINCT? Also a screenshot from the chart and the tableviever could be helpful.

- Marcus