Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

AGGR() issue

Hi community,

I have got an issue with the AGGR function in a pivot table. I reproduced the issue in the attached example, which is a simplified version of the real issue.

The idea is to select a year first using the check boxes in the top left corner. The pivot table contains two dimensions and four expression. Two expression for the year selected and two expression for the year before the year selected. The first expression of a year (YYYY SUM) gives us a simple sum of the amount for both dimensions (Niv1 and Niv2). The second expression of a year (YYYY AGGR) gives us a sum of the amount for the first dimension (Niv1) only using the AGGR() function.

The AGGR() function however is not working for Niv1 = N3 and 2011. Probably this is caused by the selection of 2012 and the fact that there is no data for Niv1 = N3 and 2012. Is there a way to overcome this issue without modifying the year selection (this is mandatory in the application)?

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: AGGR() issue

Try this instead of the aggr:

2012 AGGR: Sum(total <Niv1> Amount)

2011 AGGR: Sum({<Jaar={$(#=Jaar-1)}>} total <Niv1> Amount)


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: AGGR() issue

Thanks for your reply Gysbert, it is helpful, but it is not the solution to the issue.

The example attached to this discussion lacks a calculated dimension. The calculated dimension contains values like ‘N1’,’N2’, etc., but also some NULL values. These NULL values are suppressed, but the corresponding Amount should be taken into account for the “YYYY AGGR” expressions. This is not the case when using your “total ” suggestion.